How to populate end date based on start date

• kiran.rajenimbalkar

  SSCrazy

  Points: 2603

  More actions

  October 15, 2012 at 5:35 am

  #268546

  Hi Professional ,

  I have to populate end date based on start date of next record - 1

  So please sugget me how to do this in my below SQL

  CREATE TABLE tmp_end_date

  (ID INT,

  tgs_rec_eff_dt DATE)

  GO

  insert into tmp_end_date values(1300505,'2012-08-01 15:57:15')

  insert into tmp_end_date values(1300505,'2012-08-01 15:55:16')

  insert into tmp_end_date values(1300505,'2012-07-30 11:29:34')

  insert into tmp_end_date values(1300505,'1905-07-01')

  GO

  My SQL

  WITH aa AS

  (

  SELECT ID,tgs_rec_eff_dt FROM tmp_end_date

  )

  SELECT

  ROW_NUMBER()over(partition by ID order by tgs_rec_eff_dt) rownumber,

  ID,

  tgs_rec_eff_dt,

  NULL as end_date ---populate based on start date of next record - 1

  FROM aa

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  October 15, 2012 at 5:42 am

  #1549076

  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

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---