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