asked on

SQL Rolling 52 Week

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
FROM vcusthistory
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.

Thanks

BrandonGalderisi

Try this

declare @basedate datetime
set @basedate = dateadd(d, -((@@datefirst + datepart(dw,getdate()))%7)-1,dateadd(wk, -52,getdate()))
 
 
SELECT CustID, sum(NetSalesAmt) as NetSalesAmt, datepart(ww,trandate) as TranWeek
FROM vcusthistory
where trandate >= @basedate
and trandate < getdate()
GROUP BY CustID,DATEPART(ww,TranDate)

Open in new window

wsadfilm

ASKER

It seems to be very close to what I am looking for. I am in the process of validating the data returned and reviewing the date range it is looking at.

Is there a way to renumber the weeks so that week number 1 is reference to oldest week and week number 52 is representing last weeks data?

If I run it today the oldest week is being reported as 15,16,17&11,12,13

BrandonGalderisi

This should fix the date issue.

I also posted a function over at my other site SQL Server Nation.

http://sqlservernation.com/blogs/howtos/archive/2009/04/06/how-to-determine-the-sunday-of-a-given-week.aspx

It will give you the Sunday of a given week.

So the code would then be:

declare @basedate datetime
set @basedate = dbo.fn_SundayOfWeek (dateadd(wk,-52,getdate()))
 
SELECT CustID, sum(NetSalesAmt) as NetSalesAmt, datediff(wk,@basedate,trandate)+1 as TranWeek
FROM vcusthistory
where trandate >= @basedate
and trandate < getdate()
GROUP BY CustID,datediff(wk,@basedate,trandate)+1

Open in new window

wsadfilm

ASKER

This looks great. I just ran into 1 problem in checking the data result. When a customer does not have any sales activity for a given week, the query does not show the week in the returned result.

Is there a way to get the week number to show and the sales amt would be 0?

Thanks

BrandonGalderisi

Do you have a table of numbers? Basically you need to have a static source of numbers to fill in the gaps.

Ex:

declare @basedate datetime
set @basedate = dbo.fn_SundayOfWeek (dateadd(wk,-52,getdate()))
 
select wkn, CustID, NetSalesAmt
from
(select 1 as wkn union all select 2 union all select 3 union all select 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12
union all select 13 union all select 14 union all select 15 union all select 16 union all select 17 union all select 18
union all select 19 union all select 20 union all select 21 union all select 22 union all select 23 union all select 24
union all select 25 union all select 26 union all select 27 union all select 28 union all select 29 union all select 30
union all select 31 union all select 32 union all select 33 union all select 34 union all select 35 union all select 36
union all select 37 union all select 38 union all select 39 union all select 40 union all select 41 union all select 42
union all select 43 union all select 44 union all select 45 union all select 46 union all select 47 union all select 48
union all select 49 union all select 50 union all select 51 union all select 52
)N
left join 
(SELECT CustID, sum(NetSalesAmt) as NetSalesAmt, datediff(wk,@basedate,trandate)+1 as TranWeek
FROM vcusthistory
where trandate >= @basedate
and trandate < getdate()
GROUP BY CustID,datediff(wk,@basedate,trandate)+1
) a
on n.wkn=a.TranWeek

Open in new window

wsadfilm

ASKER

The latest code will give me a record for the missing week.

If I am returning all records then the CustID field is also null, If I am looking at a specific customer I can figure it out which customer it is, but not when I am looking at more than 1 customer. Any thoughts?

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wsadfilm

ASKER

Everything is woking perfectly - Thank you for your help.