CODEWITHSUNDEEP
sqloracle-database
user8467219
user8467219

Reputation:

MAX With CASE expression

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?

Upvotes: 3

Views: 58951

Answers (2)

Zorkolot
Zorkolot

Reputation: 2027

If the StartDate field is reliably issued an EndDate value before getting issued another StartDate (this is indicated by the small dataset referenced in the question), then a simple solution is to use a CASE statement can checks if a NULL enddate exists for the given ID:

SELECT StartDate, EndDate, ID
      ,CASE WHEN EXISTS (SELECT EndDate 
                           FROM TABLEA T2 
                          WHERE T2.ID = T1.ID AND T2.EndDate IS NULL)
             THEN 'Active'
             ELSE 'Closed'
       END AS [Current Status]
  FROM TABLEA T1

Upvotes: 0

xQbert
xQbert

Reputation: 35333

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

Upvotes: 4

Related Questions