troubleshooting Question

MS SQL Measure Duration Between Selected Rows Using A Date & Time Field

Avatar of Elisys
Elisys asked on
Microsoft SQL Server
9 Comments2 Solutions132 ViewsLast Modified:
Dear Experts,

I have a SQL table which holds alarm history data stored chronologically. There is a DateTime field entered into teach record together with the alarm label and alarm state depicting whether the alarm was ON or OFF when the record was stored.

I would like to generate a query to measure the length of time each alarm was ON over a customisable period. The result list should show this for each individual alarm recorded during the selected period.

The query which shows everything is as follows.

SELECT App_DateTime, Label, Priority, Enum_Status, Cycle FROM History

App_DateTime = date and time when record was entered into History.
Label = a unique text identifier for each alarm.
Priority = intiger depicting the priority of the alarm 0 to 4.
Enum_Status = the status of the alarm 1 = ON and 0 = OFF.
Cycle = the number of times the alarm has gone ON/OFF.

 The Label filed is unique to each alarm. Attached is a typical list of historical alarms where orange is ON and white is OFF. I need to produce a list showing the total ON time for each alarm in the period.
Sr. System Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros