Adapting Jim's sample data and query a little bit:DECLARE @Table Table (id tinyint,kduedate date,asofdate date)INSERT INTO @TableVALUES(1,'1900-01-01','2/12/2011'),(1,'2012-01-02', '2/13/2011'),(1,'1900-01-01', '2/12/2011'),(2,'1900-01-01', '2/13/2011'),(2,'1900-01-01', '2/14/2011'),(2,'1900-01-01', '2/15/2011'),(2,'2012-01-25', '2/16/2011')SELECT DISTINCT id, COALESCE(tgt.newkduedate,t1.kduedate) AS newkduedateFROM @TABLE T1 OUTER APPLY( SELECT MIN(asofdate) AS newkduedate FROM @Table t2 WHERE t1.id = t2.id AND t2.kduedate <> '19000101' AND t2.asofdate > t1.asofdate) tgtWHERE t1.kduedate = '19000101'