Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to get last 5weeks of data from currentdate Expand / Collapse
Author
Message
Posted Thursday, August 16, 2012 5:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 8, 2012 4:45 AM
Points: 2, Visits: 8
Hi,

I am not able to get count in weekwise i.e 7 days shipped orders to be one week, please check my query as follows:


Declare @CurrentDate nvarchar(50);
SET @CurrentDate='2012-07-25'

BEGIN

;WITH DateList AS
(
SELECT DATEADD(WEEK, 0, CONVERT(DATETIME, @CurrentDate)) AS CreateDateTime, 1 AS Cnter
UNION ALL
SELECT DATEADD(WEEK, -1, CreateDateTime), DateList.Cnter + 1
FROM DateList
WHERE DateList.Cnter < 5
)
SELECT DateList.CreateDateTime AS ShipWeek, COALESCE(Temp.TotalCount, 0) AS TotalCount
FROM DateList
LEFT JOIN (
SELECT COUNT(Id) TotalCount
,DATEADD(WEEK, DATEDIFF(WEEK, '19000101',CreatedDateTime),'19000101') AS ShipWeek
FROM ShipmentDetail
WHERE CreatedDateTime
BETWEEN DATEADD(DAY,-30,@CurrentDate) AND @CurrentDate
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101',CreatedDateTime),'19000101')
) Temp
ON CONVERT(VARCHAR(10), DateList.CreateDateTime, 112) = Temp.ShipWeek
END

Result Table is as follows:

ShipWeek TotalCount

2012-07-25 00:00:00.000 0
2012-07-18 00:00:00.000 0
2012-07-11 00:00:00.000 0
2012-07-04 00:00:00.000 0
2012-06-27 00:00:00.000 0
please give me the exact solution.

If i enter current date as like :

SET @CurrentDate='2012-07-30'

then result table will give data like:

ShipWeek TotalCount
2012-07-30 00:00:00.000 0
2012-07-23 00:00:00.000 1406
2012-07-16 00:00:00.000 0
2012-07-09 00:00:00.000 0
2012-07-02 00:00:00.000 0

why i am not getting if i enter current date like '2012-07-27'

Thanks&Regards,

Raghu.
Post #1345851
Posted Thursday, August 16, 2012 7:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:41 PM
Points: 2,013, Visits: 3,448
This bit: ON CONVERT(VARCHAR(10), DateList.CreateDateTime, 112) = Temp.ShipWeek

ShipWeek generated from DATEADD(WEEK, DATEDIFF(WEEK, '19000101',CreatedDateTime),'19000101') will always generate the same dates.

DATEADD(WEEK, 0, CONVERT(DATETIME, @CurrentDate)) from your CTE will generate dates 7 days apart dependent on the value of @CurrentDate.

If the two ranges don't match values, your join doesn't find any matches and the result set is empty.

Hope that makes sense!

Cheers

Post #1345946
Posted Thursday, August 16, 2012 7:28 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:43 PM
Points: 3,783, Visits: 8,482
I might be wrong and probably shouldn't give a solution with such few information. If you could provide DDL and sample data, I could test it.
This should work if your first day of the week is set to sunday and you need the counts from monday to sunday.

Notice that I changed the variable data type to avoid the explicit conversions.

Declare @CurrentDate DATETIME;
SET @CurrentDate='2012-07-25';

BEGIN
WITH DateList AS
(
SELECT DATEADD(dd, 9 - DATEPART( dw, @CurrentDate), @CurrentDate) AS CreateDateTime,
DATEADD(dd, 16 - DATEPART( dw, @CurrentDate), @CurrentDate) AS CreateDateTime2,
1 AS Cnter
UNION ALL
SELECT DATEADD(WEEK, -1, CreateDateTime),
DATEADD(WEEK, -1, CreateDateTime2),
DateList.Cnter + 1
FROM DateList
WHERE DateList.Cnter < 5
)
SELECT dl.CreateDateTime AS ShipWeek,
COUNT( sd.ID) AS TotalCount
FROM DateList dl
LEFT JOIN ShipmentDetail sd ON sd.CreatedDateTime >= dl.CreateDateTime
AND sd.CreatedDateTime < dl.CreateDateTime2
GROUP BY dl.CreateDateTime
END




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1345950
Posted Thursday, August 16, 2012 10:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 8, 2012 4:45 AM
Points: 2, Visits: 8
Thank you i got the solution with ur code
Post #1346328
Posted Friday, August 17, 2012 8:14 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:43 PM
Points: 3,783, Visits: 8,482
You're welcome, just be sure to know what it is doing before you use it for production (as you should always do). And be sure you can explain it.


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1346566
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse