mercredi 16 janvier 2019

Waterfall Case Statement

I am trying to write a case statement that returns a single value as soon as it finds the right value from a each "WHEN" statement. Once it finds a qualifying value, the query should not try to evaluate the other statements.

SELECT
    DT1.COMPANY_ID
    , CASE
    WHEN DT1.RATING_NUMBER = 1 THEN DT1.UNIQUE_ENTRY_ID
    WHEN DT1.RATING_NUMBER = 2 THEN DT1.UNIQUE_ENTRY_ID
    WHEN DT1.RATING_NUMBER = 3 THEN DT1.UNIQUE_ENTRY_ID
     END AS UNIQUE_ENTRY_ID
FROM
    RATINGS_DATA DT1

For each day, rating numbers are released for each company; sometimes all of them, sometimes not all). If the rating number is 1, it produces a corresponding unique ID. If the rating number is 2, a correspoding ID is released. If rating '1' is released the query bring the Company ID and the corresponding UNIQUE_ENTRY_ID for it. it should not bother looking at the second "WHEN" statement. If Rating Number '1' does not exist, then it tries 2 if neither exists then 3 etc. etc.

Can anyone help please?

Aucun commentaire:

Enregistrer un commentaire