Determine on time between 1 to 0 transitions

Question

My table indicates a pump ON/OFF status as follows

Value   timestamp
1       2013-09-01 00:05:41.987
0       2013-09-01 00:05:48.987
1       2013-09-01 00:05:59.987
0       2013-09-01 00:06:15.987
1       2013-09-01 00:06:34.987
etc etc. 

I need a MSSQL query that can take a months worth of these and tell me the number of minutes ON (1) and number in minutes OFF (0) i.e. duty cycle

Answer 1

Using CTE and RowNumber() function Fiddle demo:

declare @date date = '20130925'

;with cte as (
  select value, timestamp, row_number() over(order by timestamp) rn
  from table1
)
select c1.value, sum(datediff(second, c1.timestamp, c2.timestamp)) diffInSeconds
from cte c1 join cte c2 on c1.rn = c2.rn -1
where month(c1.timestamp) = month(@date) and month(c2.timestamp) = month(@date)
group by c1.value