Get last 13 months record with group sum in SQL Server

elvisidrizi1
 
on Dec 22, 2020 11:56 PM
1701 Views

Hi Everyone,

I have this code that gives me the data from the last 12 months and now I want to use it for the last 13 months or maybe being able to change it for more months in the future, I thought it would be easy if I just change some numbers in the code but apparently it does not work like that. 

here is the code for SQL:

SELECT Sum(Price) AS Total,
    DATEPART(MM, Date) AS [year],
    Sum (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) AS Lesh
FROM dbo.Expenses
WHERE Date BETWEEN DATEADD(MM, -12, GETDATE()) AND GETDATE()
GROUP BY DATEPART(MM, Date)

I would like to have a code where I can use the last 13 months, I changed the 12 number to 13 and I have the sum of 2 years. Nothing on what should've had.

Thank you

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Dec 29, 2020 06:28 AM
on Dec 29, 2020 11:43 PM
dharmendr says:
WHERE Date BETWEEN DATEADD(MM, -13, GETDATE()) AND GETDATE()

With the condition you will get the record between current data and the previous year current date - 1.

As per the record in between this the group sum will be calculated.

So if you want to calculate the complete month of from and to you need to modify the query as below.

As per the condition the record will be between 2019-11-29 18:52:56.000 and 2020-12-29 18:52:56.000.

Use below query.

SELECT SUM(Price),
DATEPART(MM, [Date]) AS [Month],
SUM (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) AS Lesh
FROM Expenses
WHERE [Date] BETWEEN DATEADD(MONTH,DATEDIFF(MONTH,0,DATEADD(MM,-12,GETDATE())),0)
AND DATEADD(SECOND,-1,DATEADD(MONTH,1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)))
GROUP BY DATEPART(MM, Date),DATEPART(YYYY, Date)

As per this condition the record will be between 2019-12-01 00:00:00.000 and 2020-12-31 23:59:59.000.

So you will get the acurate group sum as you want.