April 11, 2019 at 3:25 pm
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
April 11, 2019 at 3:53 pm
Take the MAX and MIN status, grouped by ID. You can then write your CASE expression similar to how you've done above.
John
April 11, 2019 at 10:51 pm
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.
April 12, 2019 at 8:32 pm
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.
April 17, 2019 at 9:23 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply