Show data from last 12 months from current (today) date in SQL Server

elvisidrizi1
 
on Dec 03, 2020 10:28 PM
2376 Views

Hello everyone,  I want to create a chart in vb.net that shows the cost of my expenses for the last 12 months for each month how much I have spent to be shown.

I want that it goes with my pc time, so if we have no December I want the chart to automatically show me the cost for 12 months.

SELECT Sum(Price) As Total,
      DATEPART(MM, Date) As [year],
      Sum (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as Lesh
FROM dbo.Expenses
WHERE date >=DATEADD(month,-12,DATEADD(day,DATEDIFF(day,0,GETDATE()),0)) AND date <=DATEADD(day,DATEDIFF(day,0,GETDATE()),0)
GROUP BY  DATEPART(MM, Date)

Thank you very kindly,

Elvis

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Dec 05, 2020 03:07 AM

Hi elvisidrizi1,

The query is returning the expected result as you want.

SQL

SELECT
	SUM(Price) AS Total,
	DATEPART(MM, Date) AS [year],
	ISNULL(SUM (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))), 0) AS Lesh
FROM dbo.Expenses
WHERE Date >= DATEADD(MONTH,-12,DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)) 
	AND Date <= DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
GROUP BY DATEPART(MM, Date)

Output

Total year Lesh
742.04 1 18
621.98 2 3
1009.31 3 145
1235.06 4 296
1396.23 5 364
1580.21 6 212
1195.68 7 224
560 8 0
560 9 0
859.5 10 1
560 11 0
762.02 12 6