Solved

MS SQL + Query pulling data by week

Posted on 2013-01-24
4
436 Views
Last Modified: 2013-01-24
I have a table with a date, customerid, and then dollar amount.  the date represents the start of the week.

what i am trying to do is pull a list of all the customerids with a dollar value in a week, which is the easy part.

i am then trying to pull 7 previous weeks worth of data for each customerid that is in the initial query, so the final query would show all the customerids from the first week and then 7 previous weeks of data, the other part is if the customerid doesnt have a value in a week previous then a null value would be shown.

is this possible and if so how?
0
Comment
Question by:dkilby
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38816806
is this possible and if so how?
Absolutely.  Post your SQL query as it stands now.  This way we have the correct table names and columns.
0
 

Author Comment

by:dkilby
ID: 38816901
here is the first part that pulls the initial data

Declare @reportDate datetime

select @reportDate = '01/13/2013'

select cid, dollarspent
from Customers
where dateweek = @reportDate
0
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 38817016
try this, may be there are other ways to do the same

Declare @reportDate datetime

select @reportDate = '01/13/2013'


;with CurWeek as (
select cid, sum(dollarspent) dollarspent
from Customers 
where datepart(wk,dateweek) = datepart(wk,@reportDate )
and datepart(yy,dateweek) = datepart(yy,@reportDate )
group by cid), prevWeek1 as (
select cid, sum(dollarspent) dollarspent
from Customers 
where datepart(wk,dateweek) = datepart(wk, dateadd(wk,-1,@reportDate) )
and datepart(yy,dateweek) = datepart(yy,dateadd(wk,-1,@reportDate) )
group by cid), prevWeek2 as (
select cid, sum(dollarspent) dollarspent
from Customers 
where datepart(wk,dateweek) = datepart(wk, dateadd(wk,-2,@reportDate) )
and datepart(yy,dateweek) = datepart(yy,dateadd(wk,-2,@reportDate) )
group by cid), prevWeek3 as (
select cid, sum(dollarspent) dollarspent
from Customers 
where datepart(wk,dateweek) = datepart(wk, dateadd(wk,-3,@reportDate) )
and datepart(yy,dateweek) = datepart(yy,dateadd(wk,-3,@reportDate) )
group by cid), prevWeek4 as (
select cid, sum(dollarspent) dollarspent
from Customers 
where datepart(wk,dateweek) = datepart(wk, dateadd(wk,-4,@reportDate) )
and datepart(yy,dateweek) = datepart(yy,dateadd(wk,-4,@reportDate) )
group by cid), prevWeek5 as (
select cid, sum(dollarspent) dollarspent
from Customers 
where datepart(wk,dateweek) = datepart(wk, dateadd(wk,-5,@reportDate) )
and datepart(yy,dateweek) = datepart(yy,dateadd(wk,-5,@reportDate) )
group by cid), prevWeek6 as (
select cid, sum(dollarspent) dollarspent
from Customers 
where datepart(wk,dateweek) = datepart(wk, dateadd(wk,-6,@reportDate) )
and datepart(yy,dateweek) = datepart(yy,dateadd(wk,-6,@reportDate) )
group by cid), prevWeek7 as (
select cid, sum(dollarspent) dollarspent
from Customers 
where datepart(wk,dateweek) = datepart(wk, dateadd(wk,-7,@reportDate) )
and datepart(yy,dateweek) = datepart(yy,dateadd(wk,-7,@reportDate) )
group by cid)
Select CW.cid, CW.dollarspent, PW1.dollarspent, PW2.dollarspent
, PW3.dollarspent, PW4.dollarspent, PW5.dollarspent, PW6.dollarspent
, PW7.dollarspent
From CurWeek CW Left Outer Join prevWeek1 PW1 on CW.cid = PW1.cid
Left Outer Join prevWeek2 PW2 on CW.cid = PW2.cid
Left Outer Join prevWeek3 PW3 on CW.cid = PW3.cid
Left Outer Join prevWeek4 PW4 on CW.cid = PW4.cid
Left Outer Join prevWeek5 PW5 on CW.cid = PW5.cid
Left Outer Join prevWeek6 PW6 on CW.cid = PW6.cid
Left Outer Join prevWeek7 PW7 on CW.cid = PW7.cid

Open in new window

0
 

Author Closing Comment

by:dkilby
ID: 38817122
thank you - worked exactly how i needed
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

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…
Find out what you should include to make the best professional email signature for your organization.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

713 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