How to select data based on special condition?

Question

Says I have TABLE Audit with COLUMN ID, STATUS, TIME and sample data as below:

1. {ID = 1, STATUS = 'APPROVE', TIME = '2015-02-01'}
2. {ID = 1, STATUS = 'DECLINE', TIME = '2014-12-01'}
3. {ID = 1, STATUS = 'CLOSED', TIME = '2015-11-01'}
4. {ID = 2, STATUS = 'APPROVE', TIME = '2015-02-01'}
5. {ID = 3, STATUS = 'DECLINE', TIME = '2015-10-01'}
6. {ID = 4, STATUS = 'CLOSED', TIME = '2015-02-01'}

There's a condition: If status='approve' then ignore status='decline' else select everything.

May I know how to construct a query so that I will get only records : 1,3,4,5,6?

My current way is first retrieve all data with status='approve' and 'closed' and store them into temptable, then store data that is status = 'decline' and ID not in @temptable into @temptable. Then eventually select * from @temptable.

I'm wondering if there's any other way to handle such situation?

Answer 1

You can use windowed functions:

WITH cte AS
(
SELECT *
 ,[dec] = COUNT(CASE WHEN STATUS = 'DECLINE' THEN 1 END) OVER (PARTITION BY ID)
 ,[app] = COUNT(CASE WHEN STATUS = 'APPROVE' THEN 1 END) OVER (PARTITION BY ID)
FROM #Audit
)
SELECT ID, STATUS, [TIME]
FROM cte
WHERE NOT ([dec] >= 1 AND [app] >= 1 AND [STATUS] = 'DECLINE');

LiveDemo

Your column TIME can be misleading especially when it holds date only :)