SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to get last 5weeks of data from currentdate


How to get last 5weeks of data from currentdate

Author
Message
raghava123.madhu
raghava123.madhu
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
Gazareth
Gazareth
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8006 Visits: 6046
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
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43537 Visits: 19861
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
raghava123.madhu
raghava123.madhu
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 8
Thank you i got the solution with ur code
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43537 Visits: 19861
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search