t-sql Summing differences between timestamps

Question

I'm tracking machine state which can be 0,1 and 2, and storing that data in sql table with time_stamp. I have table in sql server with next fields: id(int) time_stamp(datetime) machine_state(int)

Machine state is connected with machine condition:
machine_state =0 -machine stooped
machine_state =1-machine with alarm
machine_state =2-machine running

Now I want to calculate how long machine was in each state in each shift. Shifts are

1. 8:00-17:00
2. 17:00-01:00
3. 01:00-08:00.

My problem is how I can calculate time of each state of machine(sum_time_0, sum_time_1, sum_time_2) and group that times by the shift. I want to calculate time in seconds and then convert to minutes.

To have better picture I did export part of table

EXPORT_TABLE
id    time_stamp          machine_state
1623  6.10.2009 17:09:00  1
1624  6.10.2009 17:17:00  2
1625  6.10.2009 17:17:00  1
1626  6.10.2009 17:17:00  2
1627  6.10.2009 17:18:00  1
1628  6.10.2009 17:18:00  2
1629  6.10.2009 18:04:00  1
1630  6.10.2009 18:06:00  2
1631  6.10.2009 18:07:00  1
1632  6.10.2009 18:12:00  2
1633  6.10.2009 18:28:00  1
1634  6.10.2009 18:28:00  2
1635  6.10.2009 19:16:00  1
1636  6.10.2009 19:21:00  2
1637  6.10.2009 19:49:00  1
1638  6.10.2009 20:23:00  2

Any advice will help. Thanks in advance.

Answer 1

You can use an exclusive join to find the previous row:

select
    State = prev.ms,
    MinutesInState = sum(datediff(mi, prev.ts, cur.ts))
from @t cur
inner join @t prev
    on prev.id < cur.id
left join @t inbetween
    on prev.id < inbetween.id
    and inbetween.id < cur.id
where inbetween.id is null
group by prev.ms

The query then groups by machine state. The result differs from other answers here, I'm curious which one is right!

State  MinutesInState
1      54
2      140

Here's the sample data I used:

declare @t table (id int identity(1,1), ts datetime, ms tinyint);

insert into @t
select '6.10.2009 17:09:00',  1
union select '6.10.2009 17:17:00',  2
union select '6.10.2009 17:17:00',  1
union select '6.10.2009 17:17:00',  2
union select '6.10.2009 17:18:00',  1
union select '6.10.2009 17:18:00',  2
union select '6.10.2009 18:04:00',  1
union select '6.10.2009 18:06:00',  2
union select '6.10.2009 18:07:00',  1
union select '6.10.2009 18:12:00',  2
union select '6.10.2009 18:28:00',  1
union select '6.10.2009 18:28:00',  2
union select '6.10.2009 19:16:00',  1
union select '6.10.2009 19:21:00',  2
union select '6.10.2009 19:49:00',  1
union select '6.10.2009 20:23:00',  2