This is my table.
CREATE TABLE [dbo].[Sales]( [SalesID] [int] NOT NULL, [Quantity] [int] NULL, [Price] [numeric](18, 2) NULL, [SalesDate] [datetime] NULL ) ON [PRIMARY]
After adding few rows into the table, its looks like this.
In the above image, you can see that the last column is a date. The last row in the table has the current (July) month’s data. I wish to get the total quantity and price for the previous months only, ignoring the current.
The SQL Server Query
The query to fetch the cumulative figures of previous months will be,
SELECT DATENAME(MONTH, DATEADD(M, MONTH(SalesDate), - 1)) Month, SUM(Quantity) [Total Quanity], SUM(Price) [Total Price] FROM dbo.Sales GROUP BY MONTH(SalesDate) HAVING MONTH(SalesDate) < (SELECT DATEPART(M, DATEADD(M, 0, GETDATE())))
You may have better solution that the one I have shown here. However, this worked for me.
I am using few SQL Server in-built functions in my query.
1) Function DATENAME() – This function takes two parameters, that is, the Interval and the date. When using this function with the DATEADD() function, I am getting all the months from the date. For example,
SELECT DISTINCT DATENAME(MONTH, SalesDate) Months FROM Sales
2) Function DATEADD() – The function adds or subtracts a specified time from the date. It helps in grouping and returning the results for a distinct month.
3) Function DATEPART() – Using this function with DATEADD() function, I am getting the current month (a numeric value such as 7 for the month July).
Finally, I am using the HAVING clause to ignore the current month. The GETDATE() function will return the current date and time.
Well, that’ it. Hope this is useful.