- Home /

HI, How would I go about tracking the percentage change between the weeks?

Comment

Scot Hauder

**Answer** by Pavel Pawlowski ·

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

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?

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.

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.

Copyright 2019 Redgate Software.
Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges