SQL Query to Get Previous Months Records in SQL Server

I have some data in my Sales table. Each row in the table has data such as total quantities sold, total price and a date column, on which the figures were entered. I’ll show you how to get the previous months records using a simple query in SQL Server.

The Sales Table

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.

Get Previous Months Records in SQL Server

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 want to get the total quantity and price for the previous months only, ignoring the current month records.

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())))

The Output

Result Showing How to Get Previous Months records SQL Server

You may have different solution against the one I have shown here. However, this worked for me.

I am using few SQL Server built-in 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.

← PreviousNext →