If you just want fast and dirty, this will do:
select curr.*, prev.* from EXPORT_TABLE curr outer apply ( select top 1 * from EXPORT_TABLE prev where curr.time_stamp > prev.time_stamp order by time_stamp desc, id desc ) prev
And go from there.
But this method and some similar methods on this page related to non-equijoin will not scale well with volume. To process a large amount of data, we must use different methods.
Your identifier is displayed sequentially. It? This may be helpful. If not, we must create it.
if object_id('tempdb..#pass1') is not null drop table #pass1 create table #pass1 ( id int , time_stamp smalldatetime , machine_state tinyint , seqno int primary key -- this is important ) insert #pass1 select id , time_stamp , machine_state , seqno = row_number() over (order by time_stamp, id) from EXPORT_TABLE
As soon as we have a serial id, we can connect on it:
if object_id('tempdb..#pass2') is not null drop table #pass2 create table #pass2 ( id int , time_stamp smalldatetime , machine_state tinyint , seqno int primary key , time_stamp_prev smalldatetime ) insert #pass2 select id , time_stamp , machine_state , seqno , time_stamp_prev = b.time_stamp from #pass1 a left join #pass1 b on a.seqno = b.seqno + 1
From here, your request should be written by yourself. Pay attention to the state of the machine that overlaps the shift.
This method, although it looks expensive, will scale well with volume. You order data once and join once. If the identifier is sequential, you can skip the first step, make sure that id has a clustered primary key, and join the identifier, not seqno.
If you have a really large amount of data, you do this instead:
if object_id('tempdb..#export_table') is not null drop table #export_table create table #pass1 ( id int , time_stamp smalldatetime , machine_state tinyint , seqno int primary key -- ensures proper ordering for the UPDATE , time_stamp_prev smalldatetime ) insert #export_table ( id , time_stamp , machine_state , seqno ) select id , time_stamp , machine_state , seqno = row_number() over (order by time_stamp, id) from EXPORT_TABLE -- do some magic declare @time_stamp smalldatetime update #export_table set time_stamp_prev = @time_stamp , @time_stamp = time_stamp
This will disable all other methods. And if your identifier is in the correct order (it does not have to be sequential, only in the correct order), you can skip the first step and instead define a clustered index on id if it does not already exist.