August 16, 2012 at 5:18 am
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.
August 16, 2012 at 7:24 am
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
August 16, 2012 at 7:28 am
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
(
SELECTDATEADD(dd, 9 - DATEPART( dw, @CurrentDate), @CurrentDate) AS CreateDateTime,
DATEADD(dd, 16 - DATEPART( dw, @CurrentDate), @CurrentDate) AS CreateDateTime2,
1 AS Cnter
UNION ALL
SELECTDATEADD(WEEK, -1, CreateDateTime),
DATEADD(WEEK, -1, CreateDateTime2),
DateList.Cnter + 1
FROM DateList
WHERE DateList.Cnter < 5
)
SELECTdl.CreateDateTime AS ShipWeek,
COUNT( sd.ID) AS TotalCount
FROMDateList dl
LEFT JOIN ShipmentDetail sdON sd.CreatedDateTime >= dl.CreateDateTime
AND sd.CreatedDateTime < dl.CreateDateTime2
GROUP BY dl.CreateDateTime
END
August 16, 2012 at 10:33 pm
Thank you i got the solution with ur code
August 17, 2012 at 8:14 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply