Adding a reference data to the table column from different table line

Question

I have an event table with following columns:

• sequence (int)
• DeviceID (varchar(8))
• time_start (datetime)
• DeviceState (smallint)
• time_end (datetime)

All columns except time_end are populated with the data (my current time_end column is NULL through out the table). What I'd need to do is to populate the time_end column with the event closure data. This is actually the time when new event from the same device occurred. Here is an example data model how it should work at the end:

sequence     DeviceID         time_start           DeviceState      time_end
--------------------------------------------------------------------------------------
1            000012A7         2010-10-31 12:00     14               2010-10-31 12:10
2            000012A7         2010-10-31 12:10     18               2010-10-31 12:33
3            000012A8         2010-10-31 12:20     16               2010-10-31 13:01
4            000012A7         2010-10-31 12:33     13               2010-10-31 12:47
5            000012A7         2010-10-31 12:47     18               2010-10-31 13:20
6            000012A8         2010-10-31 13:01     20               2010-10-31 13:23
7            000012A7         2010-10-31 13:20     05               2010-10-31 14:12
8            000012A8         2010-10-31 13:23     32               2010-10-31 14:15
9            000012A7         2010-10-31 14:12     12
10           000012A8         2010-10-31 14:15     35

The idea is that for each record within the table I need to select an record on the higher sequence for specific device and update the time_end with the time_start data of that higher level record. With this I'll be able to track the time period of each event.

I was thinking on doing this with a function call, but I have two main difficulties: 1. getting the data from e.g.: sequence=2 and updating the time_end of sequence=1 2. creating a function which will do this continuously as new records are added into the table

I'm quite new to the SQL and I'm quite lost on what else is possible. Based on my knowledge I should use the function which would reference the data together, but my current knowledge is limiting me in doing that.

I hope someone could provide me some guidance into which direction to go and to provide me some feedback if I'm on the right track or not. Any support articles would be very much appreciated.

Answer 1

View:

CREATE VIEW tableview AS
with timerank AS
(
SELECT mytable.*, ROW_NUMBER() OVER (PARTITION BY DeviceID ORDER BY time_start) as row
FROM THE_TABLE mytable
)
SELECT tstart.*, tend.time_start AS time_end
FROM timerank tstart 
    LEFT JOIN timerank tend ON tstart.row = tend.row - 1 
    AND tstart.DeviceID = tend.DeviceID

Edit: I see your deviceID requirement now.

Answer 2

@OMG Ponies: I think here will be a bit better formatting:

UPDATE YOUR_TABLE SET time_end = (SELECT TOP 1 t.time_start FROM YOUR_TABLE t WHERE t.DeviceID = YOUR_TABLE.DeviceID AND t.time_start > YOUR_TABLE.time_start ORDER BY t.time_start ASC)