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!