locked
Calculating time difference between 2 events RRS feed

  • Question

  • Hi,

    I am trying to do some analysis on some log files from the machines in our factory. I have written a script to import the files into a table but now I need to do some further analysis, I am a little stuck. I have been searching but haven't quite managed to find the answer.

    Here's an example the Log File has an entry for an alarm "ALARM ON" with a date and time.

    Some other stuff happens in the log then there is an "ALARM OFF" entry with a date and time.

    I want to record the time difference against the ALARM ON event (because this is the row with the alarm description) there is no unique ID that ties the 2 events together so I simply have to ensure that data is in chronological order and find the next "ALARM OFF Event.

    I have tried to illustrate what I am aiming for below. I chose a relatively simple sequence to make the illustration easy, but there could be more "noise" that this between the events.

    I would be grateful for any pointers on this.

    Many thanks

    Tuesday, July 28, 2020 3:13 PM

Answers

  • Sure, change CROSS APPLY to OUTER APPLY to get this case as well and look for the NULL in second row result. I'll let you finish that logic on your own :)

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Stevieboy1 Tuesday, August 4, 2020 6:48 AM
    Friday, July 31, 2020 2:34 PM

All replies

  • select A.*, datediff(second, cast(A.Date as datetime) + cast(A.Time as Datetime), A1.DateTime) as [Time elapsed (sec.), A1.[Date], A1.[Time]

    from dbo.AlarmsInfo A 

    cross apply (select top (1) *, cast(a1.date as Datetime) + cast(a1.Time as DateTime) as [DateTime]

    from dbo.AlarmsInfo A1 where A1.[Event] = 'ALARM-OFF' and (A1.Date > A.Date or (A1.Date ==A.Date and A1.Time > A.Time) order by A1.Date, A1.Time) A1

    where A.Event = 'ALARM-ON'

    -------------------------------

    From the top of my head the first idea. 

     

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 28, 2020 3:24 PM
  • Hi Stevieboy1,

    create table test
    (jobtime time,
    job char(15))
    insert into test values('06:41:55','on'),('06:41:55','on'),('06:42:02','off'),('06:42:02','off'),
                           ('06:43:09','on'),('06:43:09','on'),('06:43:17','off'),('06:43:17','off')
    
    with cte 
    as(select *, row_number() over(order by jobtime) rn from test)
      ,cte1 as (select a.jobtime jobtime,a.job job,a.rn rn,datediff(ss,a.jobtime,b.jobtime) diff
                from cte a left join cte b on a.rn + 2 = b.rn)
    
    select jobtime,job, case when rn=1 or rn%5=0 then diff else null end diff from cte1
    
    drop table test 
    

    Best Regards
    Echo


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 29, 2020 9:22 AM
  • Hi

    As per my understanding you need the timedifference between on and off .Try the bellow query

    create table Customer
    (
    CustomerId int,
    CustomerHistory  varchar(100)
    )
    
    insert into Customer(CustomerId,CustomerHistory)values(1,'10% internal/90% external'),(2,'10% internal or 90% external')
    
    
    select CustomerId, CustomerHistory
    from  Customer
    where CustomerHistory like '%/%'
    
    Create table alarm
    (
     start_at TIME (0) NOT NULL,
     evtstatus varchar(50),
     
     duration int
    )
    
    insert into alarm(start_at,evtstatus)values('06:41:53','JOB-OFF')
    
    insert into alarm(start_at,evtstatus)values('06:41:55','ALARM-ON'),('06:41:55','ALARM-ON'),('06:42:02','ALARM-OFF'),('06:42:02','ALARM-OFF')
    ,('06:43:09','ALARM-ON'),('06:43:09','ALARM-ON'),('06:43:17','ALARM-OFF'),('06:43:17','ALARM-OFF')
    
    
    ;with ect(start_at,evtstatus,duration,rno)as(
    select * , 
    ROW_NUMBER() OVER (
          PARTITION BY start_at
          ORDER BY evtstatus
       ) row_num
    from alarm
    ),
    ect1(start_at,evtstatus,duration,rno)as
    (
    select start_at,evtstatus,duration, 
    ROW_NUMBER() OVER (
    	ORDER BY start_at
       ) rno
    from ect where evtstatus = 'ALARM-ON' and rno = 1
    ),
    ect2(start_at,evtstatus,duration,rno)as
    (
    select start_at,evtstatus,duration,
    ROW_NUMBER() OVER (
    	ORDER BY start_at
       ) rno
    from ect where evtstatus = 'ALARM-OFF' and rno = 1
    )
    
    select e1.start_at,e1.evtstatus,e2.start_at,e2.evtstatus, DATEDIFF(SECOND, e1.start_at,e2.start_at) as secs from ect1 e1
    
    inner join ect2 e2 on e1.rno = e2.rno
    

    Thanks and regards

    Wednesday, July 29, 2020 1:22 PM
  • Thanks for this a brilliant first idea :-) It almost worked with a few adjustments. It didn't like cast, so I used convert instead.

    One issue I have, which is more bad data than a problem with the query is that in one example there was an "ALARM ON" followed by another "ALARM ON" so the query used the next "ALARM OFF" entry to calculate both. In this scenario I am not sure if I should be calculating a duration of 0 for the first alarm or if this is even possible?

    Here is the query and the results.

    Wednesday, July 29, 2020 3:01 PM
  • I've been thinking last night it could have been the case.

    Let's change the approach a bit:

    select A.*, case when A1.[EVENT] = 'ALARM-ON' THEN 0 ELSE datediff(second, 
    
    cast(A.Date as datetime) + cast(A.Time as Datetime), A1.DateTime) END as [Time elapsed (sec.), A1.[Date], A1.[Time]
    
    from dbo.AlarmsInfo A 
    
    cross apply (select top (1) *, cast(a1.date as Datetime) + cast(a1.Time as DateTime) as [DateTime]
    
    from dbo.AlarmsInfo A1 where A1.[Event] IN ('ALARM-OFF', 'ALARM-ON') and (A1.Date > A.Date or (A1.Date ==A.Date and A1.Time > A.Time) order by A1.Date, A1.Time) A1
    
    where A.Event = 'ALARM-ON'

    It means if the next after ALARM ON is another ALARM ON (and not off) the difference will be 0


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 29, 2020 5:42 PM
  • Hi Stevieboy1,

    Could you please share us  your table structure (CREATE TABLE …) and some sample data(INSERT INTO …) ? So that we’ll get a right direction and make some test.Besides,If you can provide the code instead of the screenshot, it will be more convenient for us.

    Best Regards
    Echo


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 30, 2020 6:58 AM
  • Hi Stevieboy1,

    Has your problem been solved? If it is solved, please mark the point that you 
    think is correct as an answer. This can help others who encounter similar problems.


    Best Regards
    Echo

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, July 31, 2020 3:26 AM
  • Thanks so much Naomi,

    This is almost there. I had an instance where there was an "ALARM ON" but no subsequent "ALARM OFF" so the line didn't appear. In this instance the "ALARM ON" was an emergency stop at the end of the day (I am guessing a lazy operator who didn't shut the machine down properly).

    Can we get this to come in too? Or is this a stretch too far? :-)

    Friday, July 31, 2020 7:11 AM
  • Sure, change CROSS APPLY to OUTER APPLY to get this case as well and look for the NULL in second row result. I'll let you finish that logic on your own :)

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Stevieboy1 Tuesday, August 4, 2020 6:48 AM
    Friday, July 31, 2020 2:34 PM
  • Hi Stevieboy1,

    Do you have any updates? If not ,please mark the point that you 
    think is correct as an answer. This can help others who encounter similar problems.Thanks.

    Best Regards
    Echo  

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, August 3, 2020 9:15 AM
  • Many thanks Naomi, you're a star :-)

    Hope this solution helps someone else.

    Tuesday, August 4, 2020 6:49 AM