SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Function to calculate dynamic last 12 months total
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sathyarangaraj
Starting Member

India
11 Posts

Posted - 10/05/2012 :  10:19:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/05/2012 :  10:26:28  Show Profile  Reply with Quote
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;
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 10/05/2012 :  12:23:25  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000