Solved

SQL Rolling 52 Week

Posted on 2009-04-06
8
1,297 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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now