SQL: Flag when a user has a status of approved or denied

user_19251580 0
Viewed 38

I am building a dashboard to track how many people have been approved/denied and need to create a view to flag when someone has a status of 'approved' or one of many status that maps to 'denied'. The data looks something like this.

person_id status status_date
1 In Progress 5/1/22
1 Sent 5/2/22
2 In Progress 5/1/22
3 Sent 5/2/22
3 Denied 5/3/22
3 Appealed 5/4/22
3 Approved 5/5/22
4 Sent 5/4/22
4 Administrative Denial 5/5/22

I want to create a view that outputs something like this where there is one row per id and flags for whether the user has a status of approved and/or denied

person_id approved denied
1 0 0
2 0 0
3 1 1
4 0 1
sql-server sql
1 Answers

The solution here is called conditional aggregation:

SELECT person_id
    , MAX(CASE WHEN status='Approved' THEN 1 ELSE 0 END) As approved
    , MAX(CASE WHEN status IN ('Denied', 'Administrative Denial', 'etc') THEN 1 ELSE 0 END) as denied
FROM [MyTable]
GROUP BY person_id

See it work here:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=cd1f55b01c437cdbfa40507dd14ede4a

If would be better to have the status values in a table we could JOIN to, where the approved/denied result was one of the columns.

Joel_Coehoorn_30430
Related