Solved

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

Posted on 2014-11-27
9
73 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 45

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
 
LVL 32

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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 32

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 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

21 Experts available now in Live!

Get 1:1 Help Now