Solved

MS SQL + Query pulling data by week

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
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.

895 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

15 Experts available now in Live!

Get 1:1 Help Now