November 14, 2012 at 10:46 am
Hello Everyone
I am working on a query that uses a Computed Value to determine the Value for the case statement. What I need is to be able to use a Range to determine those values
This is what I have currently. Generic CASE statement
CASE ABS (DATEDIFF(day, t1.admitdate, t2.dischargedate))
WHEN 0 THEN 1
WHEN 8 THEN 2
WHEN 15 THEN 3
WHEN 22 THEN 4
ELSE 5
END
What I really need is to be able to specify a range of values for the WHEN clause. Like this :
CASE ABS (DATEDIFF(day, t1.admitdate, t2.dischargedate))
WHEN 0-7 THEN 1
WHEN 8-14 THEN 2
WHEN 15-21 THEN 3
WHEN 22-30 THEN 4
ELSE 5
END
But I am not able to figure out how to implement the numeric range in the WHEN clause. All that I get so far are syntax errors. The SQL BOL gives only a generic CASE statement example. I am not opposed to using Greater than, Less Than, Between, or what ever can make this work.
Thank you in advance for your help
Andrew SQLDBA
November 14, 2012 at 10:52 am
You need to change your case statement a little to be like this "CASE WHEN ABS (DATEDIFF(day, admitdate, dischargedate)) BETWEEN 0 AND 7 THEN 1"
A small optimization that may speed things up depending on the optimizer is to use a cross apply and only have to calculate the value once for each CASE switch.(is that the correct term for a WHEN in a CASE?)
WITH SampleData AS (SELECT admitdate, dischargedate
FROM (VALUES ('2012-01-01','2012-01-02'),
('2012-01-01','2012-01-09'),
('2012-01-01','2012-01-15'),
('2012-01-01','2012-01-27'),
('2012-01-01','2012-02-25')
)x(admitdate, dischargedate))
select admitdate, dischargedate,
CASE WHEN b.LengthOfStay BETWEEN 0 AND 7 THEN 1
WHEN b.LengthOfStay BETWEEN 8 AND 14 THEN 2
WHEN b.LengthOfStay BETWEEN 15 AND 21 THEN 3
WHEN b.LengthOfStay BETWEEN 22 AND 30 THEN 4
ELSE 5
END
from SampleData a
CROSS APPLY (SELECT ABS (DATEDIFF(day, a.admitdate, a.dischargedate)))b(LengthOfStay)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 14, 2012 at 10:55 am
Thank You
I was doing just that probably as you were typing your suggestion. Which works perfectly by the way.
Sometime, I either find an example, or I try something else, right after I ask for assistance.
Thanks
Andrew SQLDBA
November 14, 2012 at 10:57 am
glad to help. its one of those things that some days you just cant find on google untill you post the question. then the next search you run you find your answer.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 14, 2012 at 11:34 am
That is exactly what happened.
I don't waste too much time Searching, I know the real SQL minds are here, so I post, and I usually get faster response
Thanks again
Andrew SQLDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply