A Simple SQL Query to Get Previous Months Records in SQL Server

Here’s a simple SQL Server tip, I thought would be useful for beginners. Let us assume I have a Sales table with few records. Each row in the table has data such as total quantities sold, total price and a column with date, 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 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())))

The Output

Result Showing How to Get Previous Months records SQL Server

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.

Related Posts:

Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Facebook Google+

Join our Google Plus Community and be a part of a discussion!