Hi Expert,

Its worked but failes for below data....when i was inserting and testing

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)

insert into #tmp_end_date values(33333,'31-Jan-2012',NULL,2)

insert into #tmp_end_date values(33333,'12-Dec-2011',NULL,1)

insert into #tmp_end_date values(1300470,'2012-10-08 12:59:36',NULL,3)

insert into #tmp_end_date values(1300470,'2012-10-08 12:59:03',NULL,2)

insert into #tmp_end_date values(1300470,'1905-07-01',NULL,1)

WITH A

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

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