• PLS TRY BELOW CODE:

    CREATE TABLE #tmp_end_date

    (ID INT,

    tgs_rec_eff_dt DATE,

    tgs_rec_end_dt DATE,

    SEQNUM INT )

    GO

    insert into #tmp_end_date values(12345,'15-Mar-2012',NULL,4)

    insert into #tmp_end_date values(12345,'10-Feb-2012',NULL,3)

    insert into #tmp_end_date values(12345,'2-Feb-2012',NULL,2)

    insert into #tmp_end_date values(12345,'05-Jan-2012',NULL,1)

    ;WITH A

    AS(SELECT *,DENSE_RANK()OVER(PARTITION BY ID ORDER BY tgs_rec_eff_dt DESC ) RANK FROM #tmp_end_date)

    --SELECT * FROM A

    SELECT T.ID,T.tgs_rec_eff_dt,(SELECT DATEADD(DD,-1,T1.tgs_rec_eff_dt) FROM A T1

    WHERE T1.RANK+1=T.RANK),SEQNUM FROM A T

    ORDER BY 2 DESC