Solved

SQL Rolling 52 Week

Posted on 2009-04-06
8
1,381 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:wsadfilm
  • 4
  • 4
8 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24077322
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

0
 

Author Comment

by:wsadfilm
ID: 24078229
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
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24078648
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

0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:wsadfilm
ID: 24079234
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
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24079290
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

0
 

Author Comment

by:wsadfilm
ID: 24079965
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?
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 24080050
You need to get every customer matched up with every week, then left join to the results.

Try:
declare @basedate datetime
set @basedate = dbo.fn_SundayOfWeek (dateadd(wk,-52,getdate()))
 
select n.wkn, c.CustID, a.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
cross join (select custID from Customers) c
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
and c.custid=a.custid

Open in new window

0
 

Author Closing Comment

by:wsadfilm
ID: 31567022
Everything is woking perfectly - Thank you for your help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question