CASE WHEN

Question

CASE WHEN

dramaqueen

SSCommitted

Points: 1569
More actions
October 28, 2015 at 10:13 am

#399238

Hello,
Me again really sorry.
I am trying to script a case when to achieve the following.
I have a table of measures with certain threshold. The threshold direction can either be > or < so I want to create a field that shows if the measure hits that threshold or not to be later picked up in SSRS. So a nested case when?
CASE WHEN M.[Threshold Direction] = '>' THEN
CASE WHEN A.[Value] > M.[Threshold] THEN 'GREEN'
CASE WHEN A.[Value] < M.[Threshold] THEN 'RED'
ELSE ''
END END END AS 'Condition'
Is this at all possible?
Paul

Viewing 3 posts - 1 through 2 (of 2 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

dramaqueen (10/28/2015)
Hello,
Me again really sorry.
I am trying to script a case when to achieve the following.
I have a table of measures with certain threshold. The threshold direction can either be > or < so I want to create a field that shows if the measure hits that threshold or not to be later picked up in SSRS. So a nested case when?
CASE WHEN M.[Threshold Direction] = '>' THEN
CASE WHEN A.[Value] > M.[Threshold] THEN 'GREEN'
CASE WHEN A.[Value] < M.[Threshold] THEN 'RED'
ELSE ''
END END END AS 'Condition'
Is this at all possible?
Paul

Yes you can but the next dev to work on the same code might hunt you down and kill you. This way of doing the same thing is much easier to follow:

CASE

WHEN M.[Threshold Direction] = '>' AND A.[Value] > M.[Threshold] THEN 'GREEN'

WHEN M.[Threshold Direction] = '>' AND A.[Value] < M.[Threshold] THEN 'RED'

ELSE NULL END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

dramaqueen SSCommitted Points: 1569 More actions · Answer 2

So this will first look to see if the operator is '>', if it is then it will work out if the value is greater than the threshold and make the show green otherwise it will calculate if the value is less than the threshold and show red?

Is that the logic this follows?

Thanks for your help by the way.