Elisys
asked on
MS SQL Measure Duration Between Selected Rows Using A Date & Time Field
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.
2014-11-27-15-18-37.jpg
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.
2014-11-27-15-18-37.jpg
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
hmm, sure that we don't look at already aggregated data? The cycle field contains how often an alarm went on. Looks like an increment. So I don't think we can answer your question.
ASKER
Philip - Yes cycles 6 and 7 show two consecutive ON entries. This should not happen and may be caused by some other outside factor, or latency in the data acquisition. Assume we will fix that and that there should always be an OFF state in between two ON states.
ste5an - the cycle is an incremented value but not sure why we need to use it in the query. Can we not base the query on DateTime, Label and Enum_Status?
Vitor/HainKurt - I will try these out and comment back. Thanks.
ste5an - the cycle is an incremented value but not sure why we need to use it in the query. Can we not base the query on DateTime, Label and Enum_Status?
Vitor/HainKurt - I will try these out and comment back. Thanks.
The systems I know, which produce a similar output, send already cumulated data. Thus the question "the length of time each alarm" is not answerable. Cause we are not able to determine an event from this kind of data.
ASKER
ste5an - each entry into the database is an event in itself either ON or OFF but I do understand where you might be coming from. We are not 'trending' the data which would be cumulative. This is just an event log with dates and alarm status for each event entry. Yes the 'cycle' field is cumulative since it increments each time an ON condition has occurred but I don't believe we need to include it in the query since you will not know which proportion of the time in between two cycles it was OFF or ON. The query should extract the time between an ON state and an OFF state and then SUM these over the selected dates. Thanks, Gary
ASKER
HainKurt - the 'rn' was not recognised so I inserted 'as rn' after each sub-query. I then used the dates '01/11/14' and '26/11/14' as the @date1 and @date2. The query ran ok but gave a blank screen. What does the 'x' mean in the statement 'x where rn =1'.
oops, i forgot to add rn here :)
row_number() over (partition by Label order by App_DateTime)
>>>
row_number() over (partition by Label order by App_DateTime) rn
it is row number, those subqueries are to find first & last row for each label for a given time period
row_number() over (partition by Label order by App_DateTime)
>>>
row_number() over (partition by Label order by App_DateTime) rn
it is row number, those subqueries are to find first & last row for each label for a given time period
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
It looks like it is was ON at 11:26:31, ON again at 17:26:55, and OFF at 17:34:55.
It was then ON at 17:36:!2, ON again at 17:37:45, and OFF at 17:37:54.
What answers would you expect?