Year over Year Percent Change - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Year over Year Percent Change

Author

Topic

Andrewra
Starting Member

17 Posts

Posted - 2009-07-15 : 17:04:08

So I am stuck with this doing this calculation in SQL. If anyone can give me a push in the right direction I would appreciate it.

Year over year % change
(Current sales - Previous Sales) / Previous Sales

The problem I am having ( Im sure its something little Im just forgetting) is isolating the month to do the right comparisons. For example comparing May 2009 sales to May 2008 sales.

Thanks for any guidence
AA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-16 : 13:52:38

what are your table fields?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-16 : 13:59:08

assuming your date field is date, it will be something like


SELECT DATEADD(mm,DATEDIFF(mm,0,date),0) AS MonthDate,SUM(SalesField) AS Sales INTO #Temp
FROM YourTable
WHERE date >= DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)
AND date<DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
GROUP BY DATEADD(mm,DATEDIFF(mm,0,date),0)

SELECT (t.Sales-t1.Sales)*100.0/t1.Sales
FROM #Temp t
CROSS APPLY (SELECT Sales
             FROM #Temp
             WHERE MONTH(MonthDate)=MONTH(t.MonthDate)
             AND YEAR(MonthDate)=YEAR(t.MonthDate)-1
             )t1

Andrewra
Starting Member

17 Posts

Posted - 2009-07-16 : 14:17:16

Thanks I managed to work it out by doing a recursive join on the table and joining on the date range A.Datekey = (B.Datekey + 10000), The DateKey is formated as an Int so thats why I did + 10000 to compare 2009 to 2008

Andrew Alexander
LiveLogic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources