Retrieve last 7 days date question

Question

Retrieve last 7 days date question

PSB

SSChampion

Points: 13195
More actions
March 26, 2014 at 5:29 am

#287033

Hi,
The following query was used for retrieving dates for the last 7 days . Untill February this query was running fine and would return the last seven days date including today.
SELECT DISTINCT TOP 7 Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)) AS DateCreated,
datepart(dw,DateCreated) AS WeekNum from [TechnologyRepository].[helpdsk].[WorkDetails]
WHERE DATEDIFF(DAY, Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)),GETDATE()) <= 7
However from March (not sure of the exact date)..the query below would only give us 7 days until yesterday..i.e it would list dates from 3/19,3/20,3/21,3/22,3/23,3/24,3/25 and not 3/26 ..
I changed the query to <= 6 and it works as expected. But still not sure why it would not return todays date with <= 7.
SELECT DISTINCT TOP 7 Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)) AS DateCreated,
datepart(dw,DateCreated) AS WeekNum from [TechnologyRepository].[helpdsk].[WorkDetails]
WHERE DATEDIFF(DAY, Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)),GETDATE()) <= 6
Please help.
Thanks,
PSB

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

What if you drop the TOP 7 clause?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

PSB SSChampion Points: 13195 More actions · Answer 2

If I drop TOP 7 clause, it would list dates from 3/19 through 3/26.. a total of 8 days.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

OK, than you just need to modify your WHERE clause.

You have WHERE DATEDIFF(DAY, Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)),GETDATE()) <= 7, so this means datediff can return a number between 0 and 7, making 8 rows in total.

Either write <7 or <= 6.

The reason the date of today is missing is probably because you filtered 8 rows out with the WHERE clause, but since you also specified TOP 7, you dropped one row.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

PSB SSChampion Points: 13195 More actions · Answer 4

Thanks!

SELECT DISTINCT TOP 7 Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)) AS DateCreated,

datepart(dw,DateCreated) AS WeekNum from [TechnologyRepository].[helpdsk].[WorkDetails]

WHERE DATEDIFF(DAY, Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)),GETDATE()) <= 7

Order By Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)) DESC

would also work..

ScottPletcher SSC Guru Points: 101105 More actions · Answer 5

Current query is not SARGable and is somewhat convoluted as well.

SELECT DISTINCT TOP 7

Convert(DateTime, Datediff(Day, 0, DateCreated)) AS DateCreated,

Datepart(dw,DateCreated) AS WeekNum

FROM [TechnologyRepository].[helpdsk].[WorkDetails]

WHERE DateTime >= Dateadd(Day, Datediff(Day, 0, Getdate()) - 7, 0)

ORDER BY

Convert(DateTime, Datediff(Day, 0, DateCreated)) DESC

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".