Select (Get) last 12 Months data from Database in SQL Server

elvisidrizi1
 
on Dec 22, 2020 10:39 PM
1439 Views

Hi Everyone,

I have this code in SQL that I can see the last 12 years of my data in the vb.net chart and what I am trying to do is to see also the last 12 months of my data starting from the current month that we currently are.

I use this code for my last 12 years view:

SELECT Sum(Price) As Total,
    DATEPART(yyyy, Date) As [year],
    Sum (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as Lesh	
FROM dbo.Expenses
WHERE Date >= DATEADD(year, -12, GETDATE()) 
GROUP BY DATEPART(yyyy, Date)

and I want something similar to this but to give me the last 12 months of my data.

Thank you for your help.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Dec 22, 2020 10:52 PM

Hi elvisidrizi1,

Add between condition. Use below query. 

SQL

SELECT Sum(Price) AS Total,
    DATEPART(yyyy, 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(yyyy, Date)

 

elvisidrizi1
 
on Dec 22, 2020 11:00 PM

Thank you for your reply.

More of less is the code that I needed.

here is the code that works for me now

thanks to your help

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)