Creating new date field dynamically from next row

Question

I have a table of data. I have a field which shows date. I had set this column as Start Date. I want to create an additional column as End Date, where the End Date will be the Start Date of the next row. Can you give me a query of creating the End Date by taking the data of the Start Date in next row ?

Answer 1

First of all, you have to come up with a definition of "order", since rows in a table are stored without any order.

When you know what your order is, you can create a stored procedure that goes:

insert into the_table (new_id, start_date) values (@id, @start_date);

update the_table
set end_date = @start_date
where id = <the id determined by your sorting rule>;

Answer 2

I'm assuming that you currently have rows with values such as

StartDate
---------
1 Jan 1990
2 June 1998
4 September 2006

And you want to change to

StartDate              EndDate
---------              ------------
1 Jan 1990             2 June 1998
2 June 1998            4 September 2006
4 September 2006       NULL

Quite apart from the redundancy and maintenance issue that reminds me of this question where such a setup with correlated columns actually caused the original poster problems when querying data. (I prefer Unreason's answer to my own on that question!)

Why do you need to add the EndDate column? It will probably be possible to come up with a query that works without it.

Edit After much faffing about with row_number() I actually couldn't find a query with a better plan than this. (Assumes index on StartDate)

SELECT 
        id,
        StartDate, 
           (SELECT MIN(StartDate) 
            FROM  testTable t2 
            WHERE t2.StartDate > t1.StartDate) AS EndDate
FROM testTable t1

Answer 3

I'm answering this question because it is being referenced somewhere else.

Depending on the id having no holes is dangerous. identity columns can have gaps which the currently accepted answer does not take into account.

In SQL Server 2012+, the answer is simply lag(). In earlier versions, you can use cross apply:

Update T1
    Set T1.EndDate = T2.StartDate
    From [Table] T1 cross apply
         (select top 1 t2.*
          from [Table] T2 
          where t2.StartDate > t1.Startdate
          order by t2.StartDate asc
         ) t2;

With an index on table(StartDate), this might even have reasonable performance.

Answer 4

What about last row? The endDate will be blank for that?

Answer 5

Assuming you already have your columns and that you have an Auto-Incrementing Primary Key:

Update T1
Set T1.EndDate = T2.StartDate
From [Table] T1
Inner Join [Table] T2 on T1.Id = T2.Id - 1

Answer 6

Depends on what you mean by "next" row.

Can you provide sample dataset, and specify how you determine what order the rows go in?

EDIT

Your record order really does matter -- you're going to have to determine what that is. For now, I'm working off of the assumption that ordering it by start_date is acceptable.

--GET the first relevant start date
declare @start datetime
set @start = select MIN(start_date) from table

declare @end datetime
set @end = @start

WHILE @end is not null

  --GET the next relevant end date
  SET @end = select MIN(start_date) from table where start_date > @start

  --Update the table with the end date
  UPDATE table
  SET end_date = @end
  WHERE start_date = @start

  --GET the next relevant start date
  SET @start = @end

END