Déterminer à l'heure entre 1 et 0 transitions

Mon tableau indique le statut ON / OFF de la pompe comme suit

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. 

J'ai besoin d'une requête MSSQL qui peut prendre un mois de ces valeurs et me dire le nombre de minutes ON (1) et le nombre de minutes OFF (0) ie cycle de service

Utilisation de la fonction CTE et 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 

Essayez ceci (SQL Server 2012):

 SELECT value, SUM(sec) AS sec FROM ( SELECT value, ISNULL(DATEDIFF(SECOND, timestamp, LEAD(timestamp,1) OVER (ORDER BY timestamp) ),0) sec FROM tbl ) t GROUP BY value; 

pour sql server 2005 et versions ultérieures @start et @end déclarent la plage de time à vérifier

 with data as ( select value, timestamp, row_number() over (timestamp) as aa from data where timestamp between @start and @end ) select onPayload, datediff(s,@start,@end)-onPayload as offPayload from ( select sum(datediff(s,s.timestamp, e.timestamp)) as onPayload, from data as s inner join data as e on s.aa = e.aa-1 where s.value=1 and e.value=0 ) as a