October 17, 2008 at 4:01 am
[font="Tahoma"]
For some reason I didnt pick this up earlier, but in a Case Statement I have I'm trying to group certain results into catagorys, which works well until I have just noticed that it tends to group things in wrong groups
CASE
WHEN
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 4
THEN 1
WHEN
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 8
THEN 2
WHEN
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 13
THEN 3
WHEN
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 17
THEN 4
WHEN
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 21
THEN 5
ELSE 6
What I want to do is this
between 0&3 then 1, between 4&7 then 2, between 8&7 then 3, between 13&16 then 4, between 17&20 then 5 else 21+ then 6
I need the Weeks Waiting to always measure from the 1st of that current month, so I need th case Statement to do the same
My problem is that I have ended up with some results in the wrong groups, example I have one record that shows value 6 in Group 3 (< 13) when it should be in Group 2 (< 8)
How can I get this Case Statement to do this?
[/font]
October 17, 2008 at 5:50 am
CASE
WHEN
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 4
THEN 1
WHEN
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 >= 4
AND
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 8
THEN 2
WHEN
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 >= 8
AND
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 13
THEN 3
WHEN
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 >= 13
AND
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 < 21
THEN 4
WHEN
DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7 >= 21
THEN 5
ELSE 6
Try above code.
I can see that each statement u r writing less than 4, less than 8, etc..
now 2 is less than 4; 2 is less than 8; 2 is less than 13...so which group it will assign????
this is the confusion in ur code so make accurate category by giving AND statement as i wrote above....
it should work. i didnt test it so apologies if it ll fail.
October 17, 2008 at 6:00 am
Couldn't you ensure the distribution this way?
WHEN (DATEDIFF(DAY, [Date on Waiting List], GETDATE()) / 7) BETWEEN 4 AND 8
THEN 2
And why aren't you using WEEK (or ww or wk) for the DATEDIFF?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 17, 2008 at 6:14 am
This is almost certainly easier to test, and quite possibly quicker, if you expand it out into an onion select:
[font="Courier New"]SELECT LagDays, CASE
WHEN LagDays BETWEEN 0 AND 3 THEN 1
WHEN LagDays BETWEEN 4 AND 7 THEN 2
WHEN LagDays BETWEEN 8 AND 12 THEN 3
WHEN LagDays BETWEEN 13 AND 16 THEN 4
WHEN LagDays BETWEEN 17 AND 20 THEN 5
ELSE 6 END AS MyBracket, *
FROM (
SELECT DATEDIFF(DAY, [Date on Waiting List], GETDATE()) AS LagDays, ...
FROM...
WHERE...
) d
[/font]
Also, should you be working with business days and ignoring weekend days?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply