CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
Learner
Learner

Reputation: 4004

TSQL - populate record from the next adjacent row

Current Output

Activity            FromTime                ToTime
-------------------------------------------------------------------
STOPPED INSIDE POI  2012-11-14 01:08:46.000 2012-11-14 01:19:46.000
MOVING INSIDE POI   2012-11-14 01:20:46.000 2012-11-14 01:21:46.000
MOVING OUTSIDE POI  2012-11-14 01:22:46.000 2012-11-14 01:22:46.000
STOPPED OUTSIDE POI 2012-11-14 01:23:46.000 2012-11-14 01:23:46.000
MOVING OUTSIDE POI  2012-11-14 01:24:46.000 2012-11-14 01:25:46.000

Expected Output

Activity            FromTime                ToTime
-------------------------------------------------------------------
STOPPED INSIDE POI  2012-11-14 01:08:46.000 2012-11-14 01:20:46.000
MOVING INSIDE POI   2012-11-14 01:20:46.000 2012-11-14 01:22:46.000
MOVING OUTSIDE POI  2012-11-14 01:22:46.000 2012-11-14 01:23:46.000
STOPPED OUTSIDE POI 2012-11-14 01:23:46.000 2012-11-14 01:24:46.000
MOVING OUTSIDE POI  2012-11-14 01:24:46.000 2012-11-14 01:25:46.000

Any easy way of doing this?

Upvotes: 1

Views: 393

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44336

;with a as
(
select Activity, FromTime, ToTime, 
row_number() over (order by FromTime) rn from <yourtable>
)
select a.Activity, a.FromTime, coalesce(b.FromTime, a.ToTime) ToTime
from a left join a b on a.rn = b.rn - 1
order by a.rn

Upvotes: 1

PinnyM
PinnyM

Reputation: 35531

This should work for you:

UPDATE table1
SET ToTime = join_table.newToTime
FROM (
  SELECT t2.FromTime target, MIN(t1.FromTime) newToTime 
  FROM table1 t1 
  JOIN table1 t2 ON t1.FromTime > t2.FromTime
  GROUP BY t2.FromTime) join_table
WHERE FromTime = join_table.target

Basically you're looking for the minimum FromTime that is greater than the current row - and use that as the new value for ToTime. Note that the last row will not be updated, since no matching greater FromTime will be found.

Upvotes: 1

Related Questions