Function to calculate dynamic last 12 months total

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Function to calculate dynamic last 12 months total

Author

Topic

sathyarangaraj
Starting Member

11 Posts

Posted - 2012-10-05 : 10:19:23

Hi Techies,

I am new to SQL server. I need to calculate sum(sales) for the last 12 months. Please help

PPID Month Sales
a1 201209 50
a2 201208 100
a3 201207 75
a4 201206 100
a5 201205 112.5
a6 201204 125
a7 201203 137.5
a8 201202 150
a9 201201 162.5
a10 201112 175
a11 201111 187.5
a12 201110 200
a13 201109 212.5
a14 201108 225
a15 201107 237.5
a16 201106 250

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-05 : 10:26:28

Do it like shown below. Not sure which of the where clauses you will need - one goes to exactly one year ago from now, the other looks back 12 months starting at the beginning of the month one year ago today.

SELECT
	PPID,
	YEAR(SalesDate) AS YEAR,
	MONTH(SalesDate)AS MONTH,
	SUM(SalesAmount) AS Sales
FROM
	YourTable
WHERE
	--SalesDate >= DATEADD(YEAR,-1,GETDATE())
	Sales >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-12,0)
GROUP BY
	PPID,
	YEAR(SalesDate),
	MONTH(SalesDate)
ORDER BY
    YEAR,
    MONTH;

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-05 : 12:23:25

It looks like your SalesDate is an INT of Year and Month numbers. If that is true, then you might be able to make use of this converstion from date to int:

SELECT CAST(CONVERT(VARCHAR(6), DATEADD(MONTH, DATEDIFF(MONTH, 0, SYSDATETIME()) - 12, 0), 112) AS INT)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources