How to write the case statement on a case statement ?

  • I have this logic in my sql query.

    CASE WHEN status of id  = 'Approved'

    THEN 'APPROVED'

    WHEN status of id = 'Denied'

    THEN 'DENY'

    WHEN status of id = 'Pending'

    THEN 'PENDING'

    ELSE 'PARTIAL_APPROVE' END AS STATUS_DESC

    But the problem is there are two line id's associated with each id and each line could have different status codes

    How can I write this logic in sql ?

     

    If one line is Approved and other line is in Deny Status then the final status is  Partial Approval

    If one line is Approved and other line is in Pending Status then the final status is  Approved

    If one line is Pending and other line is in Denied Status then the final status is Denied

  • Take the MAX and MIN status, grouped by ID.  You can then write your CASE expression similar to how you've done above.

    John

  • John Mitchell-245523 wrote:

    Take the MAX and MIN status, grouped by ID.  You can then write your CASE expression similar to how you've done above. John

    Can you please provide an example ?

     

    Thank you!

  • What does "two line ids" mean? You haven't really explained how you set this up or what the data looks like. It would help to have a small repro of DDL that creates a table and data.

     

    If you're saying that for any ID you have two rows included, the you need to get these into a single row for the query. You can do this with some self join that pulls in data from both so you can evaluate each one. If you do this, you have to account for there not being a match somehow and then write code that substitutes something for the missing data.

     

  • Are we talking about nesting in CASE expression? If so, you can start another CASE after THEN. Something like this:

    CASE lineid1
    WHEN 'APPROVED'
    THEN CASE lineid2
    WHEN 'APPROVED' THEN 'APPROVED' END
    WHEN 'PENDING' THEN 'APPROVED' END
    WHEN 'Denied' THEN 'PARTIAL_APPROVE' END
    ELSE '???'
    END

    WHEN 'PENDING'
    THEN CASE lineid2
    --etc...
    END
    --etc....
    END AS STATUS_DESC

     

    --Vadim R.

  • As Steve said, you really need to post some code so we can see your table structure and sample data, etc. But what John Mitchell is suggesting might be:

    SELECT 
    id,
    CASE
    WHEN MIN([status of id]) = 'Approved' AND MAX([status of id]) = 'Denied' THEN 'Partial Approval'
    WHEN MIN([status of id]) = 'Approved' AND MAX([status of id]) = 'Pending' THEN 'Approved'
    WHEN MAX([status of id]) = 'Pending' AND MIN([status of id]) = 'Denied' THEN 'Denied'
    END AS STATUS_DESC
    FROM TableName
    GROUP BY id


    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

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

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