Complicated Case

Question

Post reply

Complicated Case

sharonsql2013

SSCertifiable

Points: 5908
More actions
May 9, 2014 at 11:24 am

#291139

Need help on this one...
I need to check two scenario's
Average Hours and Type and based on that come up with the results.
Avg is a Int , Type is Varchar.
Case when Avg < = 24 and Type = 'Type 1' Then Avg
Else 24 End
Case when Avg < 48 and (Type = 'Type2 OR Type = 'Type 3' ) Then Avg
Else 48 End
I am trying to get these two cases in One .
But am stuck with the else part because I have two separate Else part for both.

Viewing 10 posts - 1 through 9 (of 9 total)

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

mickyT SSChampion Points: 10360 More actions · Answer 1

Hi

I guessing that the type is the main part of this, so something like

CASE

WHEN Type = 'Type 1' THEN

CASE WHEN Avg <= 24 THEN Avg ELSE 24 END

WHEN Type IN ('Type 2','Type 3') THEN

CASE WHEN Avg < 48 THEN Avg ELSE 48 END

ELSE -- Some other value?

-9999

END

Edit: added missing whens

djj SSCoach Points: 18831 More actions · Answer 2

mickyT (5/9/2014)
Hi
I guessing that the type is the main part of this, so something like
CASE
WHEN Type = 'Type 1' THEN
CASE Avg <= 24 THEN Avg ELSE 24 END
WHEN Type IN ('Type 2','Type 3') THEN
CASE Avg < 48 THEN Avg ELSE 48 END
ELSE -- Some other value?
-9999
END

mickyT has a good way.

Another might be:CASE

WHEN Type = 'Type 1' AND Avg <= 24 THEN Avg

WHEN Avg <= 24 THEN 24

WHEN Type IN ('Type 2','Type 3') Avg < 48 THEN Avg

WHEN Avg < 48 THEN 48

END

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 3

Thanks for the response . But

Should we not add "When" after the 2nd and 3rd Case ?

Also , When I do so I get an error : Incorrect syntax near the keyword 'FROM '

There is something in the Case that needs to be Tweaked , Because when I comment the Case , it populates the remaining result

Here it is

Select ItemNo ,

CASE

WHEN Type = 'Type 1' THEN

CASE when Avg <= 24 THEN Avg ELSE 24 END

WHEN Type IN ('Type 2','Type 3') THEN

CASE when Avg < 48 THEN Avg ELSE 48 END

ELSE '48'

END as BelowLevel

From Prod

mickyT SSChampion Points: 10360 More actions · Answer 4

Sorry ... of course we should ... I'll fix it up.

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 5

No Worries.

I believe I forgot the second END which was causing the error.

Its Working now.. Thanks a lot!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

From what I can understand, you're trying to limit the upper value of type 1 to 24 and types 2 and 3 to 48.

This should do the trick.

Case when Avg > 24 and Type = 'Type 1'

Then 24

when Avg > 48 and (Type = 'Type2' OR Type = 'Type 3' )

Then 48

Else Avg End

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

djj SSCoach Points: 18831 More actions · Answer 7

Luis C., when I looked at the code I thought I was missing something about Avg, now I know.

mickyT SSChampion Points: 10360 More actions · Answer 8

Luis Cazares (5/9/2014)
From what I can understand, you're trying to limit the upper value of type 1 to 24 and types 2 and 3 to 48.
This should do the trick.
Case when Avg > 24 and Type = 'Type 1'
Then 24
when Avg > 48 and (Type = 'Type2' OR Type = 'Type 3' )
Then 48
Else Avg End

Nice Luis ... I thought I was over complicating it ... too early in the morning for me:-)

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 9

sharonsql2013

SSCertifiable

Points: 5908

May 9, 2014 at 1:58 pm

#1712325

Thank you all for your help. Appreciate it.