Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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.
 
Thanks&Regards,
 
Raghu.
Posted 13-Aug-12 23:09pm
Edited 13-Aug-12 23:11pm
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

What happens in your query is that the join doesn't result in any matches.
 
In your sample the DateList shows wednesdays, where the temp subquery results in mondays. You can verify this by changing the @CurrentDate into a monday. For example:
 
SET @CurrentDate='2012-07-23'
 
What you need to do is, that you convert your DateList to mondays as well. Like this:
 
WITH DateList AS
(
SELECT  DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, '19000101',@CurrentDate), '19000101')) AS CreateDateTime, 1 AS Cnter
UNION ALL
SELECT  DATEADD(WEEK, -1, CreateDateTime), DateList.Cnter + 1
FROM DateList
WHERE DateList.Cnter  < 5
)
 
I couldn't fully tests the solution, because I don't have data available. But this should work.
 

Aside from this solution there might be another thing you might look at. In the temp subquery you have the following where-clause
WHERE CreatedDateTime
BETWEEN DATEADD(DAY,-30,@CurrentDate) AND @CurrentDate
You select 30 days. But in your DateList you have 5 weeks, which is 35 days. This might result in incomplete data in the oldest week. But that actually depends on the objective you want to achieve with this query. Also when you want to use this query to get historical data, you might want to change the where clause into the following to ensure that the whole youngest week is covered (the join would filter the week you don't need).
WHERE CreatedDateTime
BETWEEN DATEADD(DAY,-35,@CurrentDate) AND DATEADD(DAY,7,@CurrentDate)
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 mhegazy94 460
1 Sergey Alexandrovich Kryukov 430
2 Ravi Bhavnani 190
3 Kornfeld Eliyahu Peter 185
4 Shemeemsha RA 160
0 Sergey Alexandrovich Kryukov 7,135
1 OriginalGriff 6,801
2 CPallini 5,350
3 George Jonsson 3,619
4 Gihan Liyanage 2,797


Advertise | Privacy | Mobile
Web03 | 2.8.140922.1 | Last Updated 15 Aug 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100