Calculating Runtime, Stop time & Breakdown time


I have been working on the machine downtime calculation project. How can I write a SQL server query to get Runtime, down time & breakdown time between a given pair of start and end timestamps.

In a transaction group, I am storing 3 tags RUN_STS, STOP_STS & BREAKDOWN_STS. At any given time only 1 of these 3 tags is TRUE. So I have 000, 001, 010, 100 as system states. I want to calculate time elapsed in each state and store it in column(s), potentially using a named query. Please find the attached image for better explanation. Thanks in advance!


Welcome to the forum. Check out the thread below along with the other links in it and see if that helps. Essentially, you'l group by your states, and sum the timestamp difference between the current row and the previous row using a lag() window function most likely.

Calculating Total Time in Seconds - Ignition - Inductive Automation Forum

1 Like