Solved

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

Posted on 2014-11-27
9
81 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 50

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 54

Accepted Solution

by:
Huseyin KAHRAMAN 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 34

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 34

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 54

Expert Comment

by:Huseyin KAHRAMAN
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

734 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