Post reply

Combining CASE to get an answer

  • July 4, 2023 at 3:41 pm

    #4226808

    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.

  • July 6, 2023 at 12:03 pm

    #4228384

    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.

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

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