Datediff and Record navigation

Hi:

I need help to find the difference between two times within the same table as follow

For every alarm incident (alarm will repeat within the table at different times)

Alarm acknowledge time:AkTime = AkAlm – InAlm

Alarm repair time:ReTime = OutAlm – Akalm

Alarm DownTime:AdtTime = OutAlm – InAlm

The table contains a decent number of records (> 10,000,000) and is indexed by the timestamp.

The table looks like this:

Alarm

AlarmType

tsTimeStapm

3030

InAlm

1/11/05 9:11:00 AM

3030

AkAlm

1/11/05 10:48:00 AM

3030

OutAlm

1/11/05 1:32:00 PM

3032

InAlm

1/11/05 2:51:00 PM

3032

AkAlm

1/11/05 2:52:35 PM

3032

OutAlm

1/11/05 3:14:00 PM

3030

InAlm

1/11/05 3:24:00 PM

3030

AkAlm

1/11/05 3:26:30 PM

3030

OutAlm

1/11/05 4:15:15 PM

Additionally

I need to calculate the mean time between alarms i.e. the time difference from the OutAlm to the next InAlm

Ex: Data from the sample table

3030

OutAlm

1/11/05 1:32:00 PM

3032

InAlm

1/11/05 2:51:00 PM

3032

OutAlm

1/11/05 3:14:00 PM

3030

InAlm

1/11/05 3:24:00 PM

Thanks in advance

Jorge

Jorge:

Maybe something like this:

declare @.alarm table
( Alarm integer,
AlarmType varchar (7),
tsTimeStamp datetime,

primary key (Alarm, tsTimeStamp),
unique (tsTimestamp, Alarm)
)

insert into @.alarm values (3030, 'InAlm', '1/11/05 9:11:00 AM')
insert into @.alarm values (3030, 'AkAlm', '1/11/05 10:48:00 AM')
insert into @.alarm values (3030, 'OutAlm', '1/11/05 1:32:00 PM')
insert into @.alarm values (3032, 'InAlm', '1/11/05 2:51:00 PM')
insert into @.alarm values (3032, 'AkAlm', '1/11/05 2:52:35 PM')
insert into @.alarm values (3032, 'OutAlm', '1/11/05 3:14:00 PM')
insert into @.alarm values (3030, 'InAlm', '1/11/05 3:24:00 PM')
insert into @.alarm values (3030, 'AkAlm', '1/11/05 3:26:30 PM')
insert into @.alarm values (3030, 'OutAlm', '1/11/05 4:15:15 PM')
--select * from @.alarm

select Alarm,
inAlm as [Alarm Timestamp],
datediff (mi, inAlm, akAlm) as [Alarm Acknowledgment Time],
datediff (mi, akAlm, outAlm) as [Alarm Repair Time],
datediff (mi, inAlm, outAlm) as [Alarm Downtime]
from ( select a.alarm,
a.tsTimeStamp as InAlm,
( select min (b.tsTimeStamp)
from @.alarm b
where a.alarm = b.alarm
and b.tsTimeStamp > a.tsTimeStamp
and b.alarmType = 'AkAlm'
) as AkAlm,
( select min (c.tsTimeStamp)
from @.alarm c
where a.alarm = c.alarm
and c.tsTimeStamp > a.tsTimeStamp
and c.alarmType = 'OutAlm'
) as OutAlm
from @.alarm a
where alarmType = 'InAlm'
) alm

-- Alarm Alarm Timestamp Alarm Acknowledgment Time Alarm Repair Time Alarm Downtime
-- -- -- - -- --
-- 3030 2005-01-11 09:11:00.000 97 164 261
-- 3030 2005-01-11 15:24:00.000 2 49 51
-- 3032 2005-01-11 14:51:00.000 1 22 23

select avg (datediff (mi, lastOutage, nextOutage)) as [Mean Time Between Alarms]
from (
select a.Alarm,
a.alarmType,
a.tsTimeStamp as [lastOutage],
( select min (tsTimestamp)
from @.alarm b
where b.alarmType = 'InAlm'
and b.tsTimestamp > a.tsTimestamp
) as [nextOutage]
from @.alarm a
where a.alarmType = 'OutAlm'
) x
where nextOutage is not null

-- Mean Time Between Alarms
--
-- 44