question

sqlLearner 1 avatar image
sqlLearner 1 asked

Calculate percentage change over weekly period

HI, How would I go about tracking the percentage change between the weeks?
sqlaggregates
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Pavel Pawlowski avatar image
Pavel Pawlowski answered
Below should be solution for SQL Server 2005 and above using CTE and ROW_NUMBER() function. Hope I didn't make any miss types as you didn't provide sample tables with some sample data and I didn't have a time to create some test tables. WITH Data AS ( SELECT s.ZACCOUNT, s.Comp_Name, c.SiteID, s.Country, c.ProductID, p.DisplayName, year(usagedate) * 100 + datepart(WW, UsageDate) as YearWeek, SUM(usages)as Total, ROW_NUMBER() OVER(PARTITION BY s.ZACCOUNT, c.SiteID, c.ProductID, p.DisplayName, s.Country, s.Name_Org1_Partner ORDER BY year(usagedate) * 100 + datepart(WW, UsageDate)) AS RowNo FROM ConUsage AS c JOIN Sites AS s ON s.SiteID= c.SiteID JOIN Products AS p ON p.ProductID= c.ProductID where YearMonth = 201002 or YearMonth = 201102 GROUP BY s.ZACCOUNT, c.SiteID, c.ProductID, p.DisplayName, s.Country, s.Name_Org1_Partner, year(usagedate) * 100 + datepart(ww, UsageDate) order by s.Zaccount, c.ProductID, YearWeek ) SELECT d1.ZACCOUNT, d1.Comp_Name, d1.SiteID, d1.Country, d1.ProductID, d1.DisplayName, d1.YearWeek AS CurrentYearWeek, d1.Total AS CurrentWeekTotal, d2.YearWeek AS PreviousYearWeek, d2.Total AS PreviousWeekTotal, ((d1.Total * 1.0 / NULLIF(d2.Total, 0) - 1) * 100) AS DifferenceInPercent FROM Data d1 LEFT JOIN Data d2 ON d1.ZACCOUNT = d2.ZACCOUNT and d1.Comp_Name = d2.Comp_Name and d1.SiteID = d2.SiteID and d1.Country = d2.Country and d1.ProductID = d2.ProductID and d1.DisplayName = d2.DisplayName and d1.RowNo = d2.RowNo + 1
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for your reply. It looks like it should work but I am getting a divide by zero error. Any thoughts how to fix that?
0 Likes 0 ·
One minor thing: if d2.Total = 0, there will be a divide by zero error (which is what the op is experiencing). It might be good to have a case statement where if d2.Total = 0, return DifferenceInPercent as 0 or NULL or something; otherwise, return the difference.
0 Likes 0 ·
Exactly as @Kevin Feasel noted. in case the Total can be 0, then you could write the calculation to return NULL. `(d1.Total * 1.0 / NULLIF(d2.Total, 0) - 1) * 100)` Or in case you want to return 0 when the d2.Total is 0 then `CASE WHEN d2.Total = 0 THEN 0 ELSE d1.Total * 1.0 / NULLIF(d2.Total, 0) - 1) * 100) END` I've update the sample with the NULLIF solution.
0 Likes 0 ·
Thank You Very Much..This is much appreciated.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.