Solved

MS SQL + Query pulling data by week

Posted on 2013-01-24
4
423 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thank you - worked exactly how i needed
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL query 4 25
Modify Table Width 6 13
encyps queries mssql 15 26
MSSQL Frequency of Years From Days Field 2 11
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

772 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

11 Experts available now in Live!

Get 1:1 Help Now