Combining CASE to get an answer

Question

Combining CASE to get an answer

PS_Richard

SSC Enthusiast

Points: 187
More actions
July 4, 2023 at 12:27 pm

#4226655
I have a 2 CASE statements where it works out if the picks are within a set threshold and give either a 'Green' or 'Red' response.
```
,CASE WHEN ISNULL (FORMAT ((SUM (CASE WHEN [CAS_AT].[Event_Code] IN (@EC01, @EC02) THEN 1.0 END) /
COUNT (CASE WHEN [CAS_AT].[Event_Code] IN (@EC01, @EC02, @EC03, @EC04, @EC05, @EC06, @EC07) THEN 1.0 END) * 1.0), @PC01), '0.00%') > '85.00%' OR
ISNULL (FORMAT ((SUM (CASE WHEN [CAS_AT].[Event_Code] IN (@EC01, @EC02) THEN 1.0 END) / COUNT (CASE WHEN [CAS_AT].[Event_Code] IN (@EC01, @EC02, @EC03, @EC04, @EC05, @EC06, @EC07) THEN 1.0 END) * 1.0), @PC01), '0.00%') = '100.00%' THEN 'Green' ELSE 'Red' END [Pick Accuracy > 85%]
CASE WHEN COUNT (CASE WHEN [CAS_AT].[Event_Code] IN (@EC07) THEN 1 END) >= '2' THEN 'Red' ELSE 'Green' END [Zero Availablity Picks >= 2]
```
However, the overall view should be looked at with 3 colours, Green, Orange or Red. I'm trying to see if there is a way to combine the two above and include some additional checks. Here is what makes them into which colour.
Green - Both the first CASE and the second CASE have to be Green to be overall Green.
Orange - The first CASE will be Red and the second CASE will be Green for an overall value of Orange.
Red - If the second CASE statement is ever Red, no matter what is in the first CASE statement the overall value will be Red.
How would I go about doing this?
Thanks as always.
July 9, 2023 at 11:20 am

This was removed by the editor as SPAM
August 29, 2024 at 4:03 pm

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

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

frederico_fonseca SSCoach Points: 16157 More actions · Answer 1

first thing is to fix the logic - NEVER EVER use FORMAT and then compare its output with something else, especially if it is not a equality compare.

on your first compare if the result of the format is "9.9%' it will be considered greater than '85.00%' which I'm pretty sure is not what you wanted.

kajaldigital8700@gmail.com SSC Rookie Points: 43 More actions · Answer 2

To achieve the desired logic, you can use nested CASE statements to combine the conditions and determine the overall color. Here's an example:

CASE

WHEN

(

ISNULL(FORMAT((SUM(CASE WHEN [CAS_AT].[Event_Code] IN (@EC01, @EC02) THEN 1.0 END) /

COUNT(CASE WHEN [CAS_AT].[Event_Code] IN (@EC01, @EC02, @EC03, @EC04, @EC05, @EC06, @EC07) THEN 1.0 END) * 1.0), @PC01), '0.00%') > '85.00%'

OR ISNULL(FORMAT((SUM(CASE WHEN [CAS_AT].[Event_Code] IN (@EC01, @EC02) THEN 1.0 END) /

COUNT(CASE WHEN [CAS_AT].[Event_Code] IN (@EC01, @EC02, @EC03, @EC04, @EC05, @EC06, @EC07) THEN 1.0 END) * 1.0), @PC01), '0.00%') = '100.00%')

AND COUNT(CASE WHEN [CAS_AT].[Event_Code] IN (@EC07) THEN 1 END) < '2'

)

OR COUNT(CASE WHEN [CAS_AT].[Event_Code] IN (@EC07) THEN 1 END) >= '2'

)

THEN 'Orange'

WHEN COUNT(CASE WHEN [CAS_AT].[Event_Code] IN (@EC07) THEN 1 END) >= '2'

THEN 'Red'

ELSE 'Green'

END AS [Overall Color]

This nested CASE statement checks the conditions for Green, Orange, and Red based on the combined logic you specified. It evaluates the first CASE condition and checks if the second CASE condition is Red or if it meets the criteria for Orange. Otherwise, it defaults to Green. Adjust the condition values as necessary for your specific requirements.