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

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

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

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

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