How to get last 5Weeks of data from User Input date in sqlserver

Question

0.00/5 (No votes)

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:

SQL

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

Madhugundi

Updated 13-Aug-12 23:11pm

AshishChaudha

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Martijn Kok · Answer 1 · 2012-08-14T22:00:00

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:

SQL

SET @CurrentDate='2012-07-23'

What you need to do is, that you convert your DateList to mondays as well. Like this:

SQL

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

SQL

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).

SQL

WHERE CreatedDateTime
BETWEEN DATEADD(DAY,-35,@CurrentDate) AND DATEADD(DAY,7,@CurrentDate)