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

elvisidr...

SOLVED CLOSED ACTIVE ANSWERED
User: elvisidrizi1
Posted: on Dec 03, 2020 10:28 PM
Forum: SQL Server
Answer: 1
Views: 9009

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

dharmend...

0 5

Reply
ANSWER
User: dharmendr
Replied: on Dec 05, 2020 03:07 AM
Report

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

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

Company

Explore

Follow us

Contact Us