InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 20:23:28 GMT20http://www.sqlservercentral.com/Forums/Topic1357750-391-1.aspxPls try below code.CREATE TABLE #table1 ( ID Nvarchar (10), Date DATE)INSERT INTO #table1 (ID,Date)SELECT '1', '12/31/2078'UNION ALLSELECT '1', '12/31/2011'UNION ALLSELECT '2', '12/31/2011'UNION ALLSELECT '2', '11/30/2011'UNION ALLSELECT '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 T1Wed, 12 Sep 2012 01:12:30 GMTsubbareddy542http://www.sqlservercentral.com/Forums/Topic1357750-391-1.aspxYou can try this:[code="sql"]SELECT ID, Date ,Status=CASE WHEN GETDATE() < MAX(Date) OVER (PARTITION BY ID) THEN 'Active' ELSE 'Inactive' ENDFROM Table1[/code]Tue, 11 Sep 2012 18:26:01 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1357750-391-1.aspxI am trying to assign Active or inactive based on member termdate. A member may have multiple termdates. The logic is if a member MAX termdate is greater than getdate() then regardless of his/her previous termdate, he/she is assigned Active. If a member Max termdate is less than getdate(), then he/she is assinged inactive. Here are DLLs:CREATE TABLE table1 ( ID Nvarchar (10), Date smalldat)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' --Results CREATE TABLE Results ( ID Nvarchar (10), Date smalldat,Status varchar(15))INSERT INTO Results (ID,Date,Status) SELECT '1', '12/31/2078' , 'Active' UNION ALL SELECT '1', '12/31/2011' , 'Active' UNION ALL SELECT '2', '12/31/2011' , 'Inactive' UNION ALL SELECT '2', '11/30/2011' , 'Inactive' UNION ALL SELECT '2', '10/31/2011' , 'Inactive'Thank for the helpHelalTue, 11 Sep 2012 18:14:38 GMThelal.mobasher 13209