Pls try below code.

CREATE TABLE #table1

(

ID Nvarchar (10),

Date DATE

)

INSERT INTO #table1

(ID,Date)

SELECT '1', '12/31/2078'

UNION ALL

SELECT '1', '12/31/2011'

UNION ALL

SELECT '2', '12/31/2011'

UNION ALL

SELECT '2', '11/30/2011'

UNION ALL

SELECT '2', '10/31/2011'

SELECT *,(SELECT CASE WHEN MAX(T2.Date)>GETDATE() THEN 'ACTIVE' ELSE 'INACTIVE' END FROM #table1 T2 WHERE T1.ID=T2.ID) STATUS FROM #table1 T1