SCADA表每天的运行时间

作者: Innate Mind
发布时间: 2024-08-05 07:14:44 (3天前)
转自：

3 条回复

0#
回复此人
尼拉。 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 您需要获取下一个值，您可以使用它 <code> outer apply </code> ： </p> <pre> <code> select s.date, sum(datediff(seconds, s.date, snext.date) / 60.0) as runtime_minutes from scada s outer apply (select top 1 date from scada s2 where s2.date > s.date and s2.status = 'Stopped' order by s2.date desc ) snext where s.status = 'Running' group by s.date; </code> </pre> </DIV>

编辑
1#
回复此人
⑩Ⅵ嵗Ｄ夨憶 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 这应该工作。 </p> <pre> <code> ;WITH CTE( [event_time], [status]) AS ( SELECT CAST('1/01/2011 5:07:34' AS DATETIME) , 'Running' UNION ALL SELECT '1/01/2011 5:14:22', 'Stopped' UNION ALL SELECT '2/01/2011 13:13:21', 'Running' UNION ALL SELECT '2/01/2011 14:14:22', 'Stopped' UNION ALL SELECT '3/01/2011 13:13:21', 'Running' UNION ALL SELECT '3/01/2011 14:14:22', 'Stopped' ) , cte_Running AS ( SELECT * FROM CTE WHERE [CTE].[status] = 'Running' ) , cte_Stopped AS ( SELECT * FROM CTE WHERE [CTE].[status] = 'Stopped' ) SELECT CONVERT(VARCHAR(10), [r].[event_time], 101) AS [date] , DATEDIFF(MINUTE, [r].[event_time], [s].[event_time]) AS [runtime_mins] FROM cte_Running AS r INNER JOIN cte_Stopped AS s ON CAST(r.event_time AS DATE) = CAST(s.event_time AS DATE) </code> </pre> </DIV>

编辑

登录后才能参与评论