MAX With CASE expression

Question

I have the below table:

Criteria:

Take EndDate of latest row per ID If EndDate is null then Active If Enddate is not null then Closed

TableA

StartDate       EndDate            ID
  04/01/2017      06/30/2017        1   
  10/01/2017                        1
  11/01/2017      05/01/2017        2

Expected Results:

StartDate       EndDate            ID    CurrentStatus
  04/01/2017      06/30/2017        1       Active
  10/01/2017                        1       Active
  11/01/2017      11/02/2017        2       Closed

Sql Code:

SELECT * 
MAX(CASE WHEN CCP.ENDDATE IS NULL THEN 'Active' END)  
         OVER (PARTITION BY CCP.ID) AS CURRENT_STATUS
FROM TABLEA CCP

Can I add an Else condition for "Closed" customers?

Answer 1

Maybe:

SELECT * 
coalesce(MAX(CASE WHEN CCP.ENDDATE IS NULL THEN 'Active' END)  
         OVER (PARTITION BY CCP.ID),'Closed') AS CURRENT_STATUS
FROM TABLEA CCP

Or if you have situations where end date is in the future and you need those records to be active...

This approach says if any ID has a null end date it must still be active of if any ID has a end date that is in the future, it must still be active.

The subquery identifies all ID's which are "active" based on not closed or end date is in the future; and then if such a record exists we use a case to either set all those ID's to either active or closed.

SELECT A1.StartDate
     , A1.EndDate
     , A1.ID
     , case when A2.ID is not null then 'Active' Else 'Closed' end As currentStatus
FROM TableA A1
INNER JOIN (SELECT Distinct ID 
            FROM TableA 
            WHERE EndDate is null or EndDate>=Sysdate()) A2
 on A1.ID = A2.ID