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
ElisysAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
HainKurtSr. System AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ste5anSenior DeveloperCommented:
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
ElisysAuthor Commented:
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
ste5anSenior DeveloperCommented:
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
ElisysAuthor Commented:
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
ElisysAuthor Commented:
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
HainKurtSr. System AnalystCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.