SQL Query to Get Previous Months Records in SQL Server

sql server

← PrevNext →

Tools you can use

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.

Related: How to Convert Month Number in a Date to Month Name in SQL Server

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.

You may also like: SQL Query to Get Last 3 Months Records in SQL Server

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.

← PreviousNext →