MS SQL + Query pulling data by week

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?
dkilbyAsked:
Who is Participating?
 
appariConnect With a Mentor Commented:
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
 
Anthony PerkinsCommented:
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
 
dkilbyAuthor Commented:
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
 
dkilbyAuthor Commented:
thank you - worked exactly how i needed
0
All Courses

From novice to tech pro — start learning today.