March 26, 2014 at 5:29 am
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
March 26, 2014 at 5:40 am
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
March 26, 2014 at 5:45 am
If I drop TOP 7 clause, it would list dates from 3/19 through 3/26.. a total of 8 days.
March 26, 2014 at 5:51 am
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
March 26, 2014 at 5:58 am
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..
March 26, 2014 at 11:34 am
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".
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy