Next record based on what? What is the ordering here?

Sample data: -

CREATE TABLE tmp_end_date(ID INT,tgs_rec_eff_dt DATETIME);

INSERT INTO tmp_end_date

SELECT ID, tgs_rec_eff_dt

FROM (VALUES(1300505,'2012-08-01 15:57:15'),(1300505,'2012-08-01 15:55:16'),

(1300505,'2012-07-30 11:29:34'),(1300505,'1905-07-01'))a(ID, tgs_rec_eff_dt);

My guess for what you want to do: -

SELECT a.ID, a.tgs_rec_eff_dt AS startDate, b.tgs_rec_eff_dt AS endDate

FROM tmp_end_date a

OUTER APPLY (SELECT TOP 1 tgs_rec_eff_dt

FROM tmp_end_date b

WHERE a.tgs_rec_eff_dt < tgs_rec_eff_dt

ORDER BY tgs_rec_eff_dt ASC) b;

Result: -

ID startDate endDate

----------- ----------------------- -----------------------

1300505 2012-08-01 15:57:15.000 NULL

1300505 2012-08-01 15:55:16.000 2012-08-01 15:57:15.000

1300505 2012-07-30 11:29:34.000 2012-08-01 15:55:16.000

1300505 1905-07-01 00:00:00.000 2012-07-30 11:29:34.000