Solved

SQL Rolling 52 Week

Posted on 2009-04-06
8
1,611 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

635 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