CODEWITHSUNDEEP
sqlsql-servert-sql
user3673034
user3673034

Reputation: 3

update end time with next start time in same table

I have a table in SQL server:

name varchar(10)
start_time timestamp
end_time timestamp

has data in table like:

John, '2014-05-26 00-00-01', null
John, '2014-05-25 15-00-01', null
Tom,  '2014-05-25 16-00-01', null
John, '2014-05-25 09-00-01', null
Tom,  '2014-05-25 03-00-01', null
..

I want to have a SQL to update end_time with next start_time from same person, after update, the result like:

John, '2014-05-26 00-00-01', null
John, '2014-05-25 15-00-01', '2014-05-26 00-00-01'
Tom,  '2014-05-25 16-00-01', null
John, '2014-05-25 09-00-01', '2014-05-25 15-00-01'
Tom,  '2014-05-25 03-00-01', '2014-05-25 16-00-01'

Is there any easy way in SQL? I think need inner join?

(some idea like:

update mytable t1
set t1.end_time = min(t2.start_time)
inner join mytable t2 on t1.name = t2.name where t1.start_time < t2.start_time and t1.end_time = null;

but does not work)

Upvotes: 0

Views: 645

Answers (2)

Jithin Shaji
Jithin Shaji

Reputation: 6073

This may help you.

DECLARE @TABLE TABLE 
        (
        NAME VARCHAR(10), 
        START_TIME DATETIME, 
        END_TIME DATETIME
        )

INSERT  INTO @TABLE (NAME,START_TIME,END_TIME) VALUES 
('JOHN', '2014-05-26 00:00:01',NULL),
('JOHN', '2014-05-25 15:00:01',NULL),
('TOM',  '2014-05-25 16:00:01',NULL),
('JOHN', '2014-05-25 09:00:01',NULL),
('TOM',  '2014-05-25 03:00:01',NULL)

UPDATE  A
SET     A.END_TIME = LU.START_TIME
FROM    (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY START_TIME DESC) [ROW] 
        FROM    @TABLE
        ) A 
LEFT JOIN   (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY START_TIME DESC) [ROW] 
        FROM    @TABLE
        ) LU 
ON      A.NAME = LU.NAME 
AND     A.ROW = LU.ROW + 1

SELECT * FROM @TABLE

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

The following query gets the next start time, using a correlated subquery (you can also use cross apply or the lead() function, if available).

select t.*,
       (select top 1 t2.start_time
        from t2.name = t.name and t2.start_time > t.start_time
        order by t2.start_time asc
       ) as next_start_time
from mytable t;

One very nice feature of SQL Server is the updatable CTE. So you can use this in an update statement:

with toupdate as (
      select t.*,
             (select top 1 t2.start_time
              from t2.name = t.name and t2.start_time > t.start_time
              order by t2.start_time asc
             ) as next_start_time
      from mytable t
     )
update toupdate
    set end_time = next_start_time;

Upvotes: 0

Related Questions