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