Help with Filtering Results from one table

Other SQL Server Topics
1

Hello,
I'm currently in the process of implementing an alarm event report displaying the time an alarm was active, the problem I'm having is filtering the report to display the correct record.
The system populating the table creates an EventAssosiationID, what this means is that a particular even will generate multiple records.

Example of Data

EventID | AssociatedID | EventTime | Active | Acked
0d84 | 78FFF | 2015-06-19 19:25:16.337 | 1 | 0
098E | 78FFF | 2015-06-19 19:25:28.390 | 1 | 1
E32D | 78FFF | 2015-06-19 19:25:39.363 | 0 | 1
E32D | 78FFF | 2015-06-19 19:45:39.363 | 0 | 0

I need to grab the first time when the alarm became Active=1 and the first time when it becomes Active=0, the problem is there are multiple records when Active=0.

I created a store procedure that will calculate the time difference but having a difficult time picking only the first Active=0 record with the earliest time.

Can anyone Help me? Thank you in advance

Snip of store procedure,

WITH AlarmFilter_CTE AS
(
SELECT eStart.EventTimeStamp AS Alarm_Start
,eEnd.EventTimeStamp AS Return_2_Normal
,eStart.SourceName AS SourceName
,eStart.Message
,eStart.TicksTimeStamp As ActiveTicks
,DateDiff(ss,eStart.EventTimeStamp, eEnd.EventTimeStamp)/60 AS Duration_Minutes
FROM
(SELECT * FROM #ALARMS WHERE Active = 1 and Acked = 0) eStart
JOIN
(SELECT EventAssociationID, EventTimeStamp,TicksTimeStamp FROM #ALARMS Where Active = 0 and Acked=1 OR Active = 0 and Acked=0)
ON
eStart.EventAssociationID = eEnd.EventAssociationID
AND eStart.EventTimeStamp <= eEnd.EventTimeStamp

SELECT
Alarm_Start AS [Alarm Start]
,Return_2_Normal AS [Return To Normal]
,Duration_Minutes AS [Alarm Duration Minutes]
FROM AlarmFilter_CTE
ORDER BY

2

I think this should work

if OBJECT_ID('tempDb..#temp') is not null drop table #temp;

go

create table #temp
(
EventId varchar(10),
AssociatedID varchar(10),
EventTime datetime2(3),
Active bit,
Acked bit 
);

insert #temp 
values
('0d84 ',' 78FFF ',' 2015-06-19 19:25:16.337 ','1','0'),
('098E ',' 78FFF ',' 2015-06-19 19:25:28.390 ','1','1'),
('E32D ',' 78FFF ',' 2015-06-19 19:25:39.363 ','0','1'),
('E32D ',' 78FFF ',' 2015-06-19 19:45:39.363 ','0','0'),
('E32D ',' Test1 ',' 2015-06-20 19:25:39.363 ','0','1'),
('E32D ',' Test1 ',' 2015-06-19 19:02:39.363 ','1','1'),
('E32D ',' Test1 ',' 2015-06-20 17:02:39.363 ','1','0')
;

go

select top 1 with ties * 
from #temp a
where not (Active = 1 and Acked = 1)
order by ROW_NUMBER() over(partition by AssociatedID, Active order by EventTime asc)

drop table #temp;
3

Here is one way:

Declare @eventTable Table (EventID char(4), AssociatedID char(5), EventTime datetime, Active bit, Acked bit);
 
Insert Into @eventTable (EventID, AssociatedID, EventTime, Active, Acked)
Values ('0d84', '78FFF', '2015-06-19 19:25:16.337', 1, 0)
     , ('098E', '78FFF', '2015-06-19 19:25:28.390', 1, 1)
     , ('E32D', '78FFF', '2015-06-19 19:25:39.363', 0, 1)
     , ('E32D', '78FFF', '2015-06-19 19:45:39.363', 0, 0);

 Select *
   From @eventTable
  Order By
        EventTime;

 Select AlarmStart = et.EventTime
      , ReturnNormal = ia.EventTime
      , DurationInSeconds = datediff(second, et.EventTime, ia.EventTime)
      , DurationInMinutes = datediff(second, et.EventTime, ia.EventTime) / 60.0
   From @eventTable             et
  Cross Apply (Select Top 1
                      *
                 From @eventTable e
                Where e.[Active] = 0
                Order By
                      e.EventTime
              )                 ia                  
  Where et.[Active] = 1
    And et.Acked = 0
4

Thank you for the replies but they do not work, my fault I forgot to mention that only records with the same ** AssociatedID ** can be tied together to generate a duration time.

5

femiolan,
I used your method to create a temporary table that I can than run a join statement and be able to produce the result I needed, thank you for your help.

1 Like
6

The version I sent will work with one additional change. In the cross apply - you need to use this:

  Cross Apply (Select Top 1
                      *
                 From @eventTable e
                Where e.[Active] = 0
                  And e.AssociatedID = et.AssociatedID
                Order By
                      e.EventTime
              )                 ia

If there is no associated ID that has a row that is not Active it will be excluded from the results. If you need that row you can change it to an outer apply and then use a derived value for the non-existing row:

  , DurationInSeconds = datediff(second, et.EventTime, coalesce(ia.EventTime, getdate()))

If you only want to include non-active (Active = 0) rows that occurred after the first Active = 1 row you can modify the apply by adding And e.EventTime > et.EventTime. This will insure that you are getting the next row and not an earlier row.