SQL Rolling 52 Week
Posted on 2009-04-06
We are running MS SQL 2000.
I have a project from our sales team in which they are asking me to pull sales trends against our customer base. The sales team is looking for a rolling 52 week report totaling the customers sales.
I am able to get the week number using datepart(ww,TranDate) and I can sum(NetSalesAmt) to get the dollars, but what I am struggling with is how to take it backwards 52 weeks. I also want to renumber the weeks so that they are 1,2,3&50,51,52 with 1 being the oldest week (last years date) and 52 being the most current data point.
My SQL statement so far:
SELECT CustID, sum(NetSalesAmt) as NetSalesAmt, datepart(ww,trandate) as TranWeek
GROUP BY CustID,DATEPART(ww,TranDate)
I am looking for help on the WHERE / HAVING to just get the last 52 weeks and the renumbering on the weeks.