Solved

MS SQL + Query pulling data by week

Posted on 2013-01-24
4
433 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
Find out what you should include to make the best professional email signature for your organization.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

831 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