Solved

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

Posted on 2014-11-27
9
77 Views
Last Modified: 2015-01-13
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
0
Comment
Question by:Elisys
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40469134
Can you explain what happened for Cycle 6 and 7?

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?
0
 
LVL 47

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 200 total points
ID: 40469152
Based on the image you attached, I came with this script:
WITH Time_CTE(CDC, W, Cycle, Duration)
AS (
	SELECT CDC, W,  Cycle, DATEDIFF(second,MIN(App_DateTime),MAX(App_DateTime))
	FROM History
	)
SELECT MIN(h.App_DateTime) as AppDateTime, h.Label, h.Priority, h.Enum_Status, h.Cycle, t.Duration
FROM History h
	INNER JOIN Time_CTE t ON t.CDC=h.CDC AND t.W=h.W AND t.Cycle=h.Cycle

Open in new window

0
 
LVL 51

Accepted Solution

by:
HainKurt earned 300 total points
ID: 40469164
first of all we need to find min & max records

select * from (
select 'S' start_end, h.*, row_number() over (partition by Label order by App_DateTime)
from History h where App_DateTime between @date1 and @date2
) x where rn=1

and this

select * from (
select 'E' start_end, h.*, row_number() over (partition by Label order by App_DateTime desc)
from History h where App_DateTime between @date1 and @date2
) x where rn=1

now lets join these two

with s as (
  select * from (
    select 'S' start_end, h.*, row_number() over (partition by Label order by App_DateTime) 
    from History h where App_DateTime between @date1 and @date2
  ) x where rn=1
),
e as (
  select * from (
    select 'E' start_end, h.*, row_number() over (partition by Label order by App_DateTime desc) 
    from History h where App_DateTime between @date1 and @date2
  ) x where rn=1
)
select * from s left join e on s.label=e.label

Open in new window


now we will have one row per label that has first & last record for a given period... from here, you can use the columsn to calculate ellapsed time as

e.App_DateTime & s.App_DateTime, like this:

seconds ellapsed : DATEDIFF(second, s.App_DateTime, e.App_DateTime)
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 33

Expert Comment

by:ste5an
ID: 40469203
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.
0
 

Author Comment

by:Elisys
ID: 40469287
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.
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40469337
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.
0
 

Author Comment

by:Elisys
ID: 40469398
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
0
 

Author Comment

by:Elisys
ID: 40469460
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'.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40469462
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
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now