Rolling 15 weeks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Rolling 15 weeks

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-06-03 : 11:17:49

Hi ,

I have the below query. I need to get the counts for the last 15 weeks. The below query gets the required results but it is going to be very long to get 15 weeks of data as right now I have code just to get for last week and the two weeks ago. I am not sure how do I modify it.

SELECT
sum(Last_Week_Customer_ID) as Last_Week_Customer_ID,
sum(Last_Week_CC)as Last_Week_CC,
sum(Last_Week_Events)as Last_Week_Events,
sum(Two_Week_Customer_ID) as Two_Week_Customer_ID,
sum(Two_Week_CC)as Two_Week_CC,
sum(two_Week_Events)as two_Week_Events
FROM
(
SELECT
COUNT(DISTINCT Customer_ID)as Last_Week_Customer_ID,
COUNT(DISTINCT CC)as Last_Week_CC,
isnull(count(*),0) as Last_Week_Events,
0 as Two_Week_Customer_ID,
0 as Two_Week_CC,
0 as two_Week_Events
FROM
dbo.Member with (NOLOCK)
WHERE
and STATUS = 'dghu'
and Date_Added >=@Start_Recd_Date
and Date_Added < @End_Recd_Date

UNION ALL

SELECT
0 as Last_Week_Customer_ID,
0 as Last_Week_CC,
0 as Last_Week_Events,
COUNT(DISTINCT Customer_ID) as Two_Week_Customer_ID,
COUNT(DISTINCT Hashed_Credit_Card_Number) as Two_Week_CC,
isnull(count(*),0) as two_Week_Events
FROM
dbo.Member with (NOLOCK)
WHERE

and STATUS = 'dghu'
and Date_Added >=dateadd(d,-7,@Start_Recd_Date)
and Date_Added < dateadd(d,0,@Start_Recd_Date)

)A

Thanks,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 11:54:47

[code]SELECT MAX(CASE WHEN Seq=1 THEN Customer_ID ELSE NULL END) AS LastWeekCustomerID,
MAX(CASE WHEN Seq=1 THEN CC ELSE NULL END) AS LastWeekCC,
MAX(CASE WHEN Seq=1 THEN Events ELSE NULL END) AS LastWeekEvents,
MAX(CASE WHEN Seq=2 THEN Customer_ID ELSE NULL END) AS LastTwoWeekCustomerID,
MAX(CASE WHEN Seq=2 THEN CC ELSE NULL END) AS LastTwoWeekCC,
MAX(CASE WHEN Seq=2 THEN Events ELSE NULL END) AS LastTwoWeekEvents,
....
MAX(CASE WHEN Seq=15 THEN Customer_ID ELSE NULL END) AS Last15WeekCustomerID,
MAX(CASE WHEN Seq=15 THEN CC ELSE NULL END) AS Last15WeekCC,
MAX(CASE WHEN Seq=15 THEN Events ELSE NULL END) AS Last15WeekEvents

FROM
(
SELECT DATEADD(wk,DATEDIFF(wk,0,Date_Added ),0) AS WeekDate,
COUNT(DISTINCT Customer_ID)as Customer_ID,
COUNT(DISTINCT CC)as CC,
isnull(count(*),0) as Events,
ROW_NUMBER() OVER (ORDER BY DATEADD(wk,DATEDIFF(wk,0,Date_Added ),0) DESC) AS Seq
FROM
dbo.Member with (NOLOCK)
WHERE
and STATUS = 'dghu'
and Date_Added >=DATEADD(wk,-15,@Start_Recd_Date)
and Date_Added < @End_Recd_Date
GROUP BY DATEADD(wk,DATEDIFF(wk,0,Date_Added ),0)
)t
[/code]

Petronas
Posting Yak Master

134 Posts

Posted - 2009-06-03 : 12:22:23

Hi Visakh16,

You are AWESOME! Thanks for the solution. It worked great!! Totally appreciate all your help and for your time..

Thanks again,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 12:34:56

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources