I have an event table structured as follows:
Column Name Type
========== ====
Timestamp datetime
SourceID nvarchar
EventID nvarchar
There is no primary key in the table.
What I'm wanting to achieve is to get the time differences between pairs of
events (Event1 & Event2).
So for example the records
01/01/2001 1:00:00 PM Source1 Event1
01/01/2001 2:00:00 PM Source1 Event2
would give me the result 3600 seconds. If the event does not have a pair,
then it would be ignored.
For example the records
01/01/2001 1:00:00 PM Source1 Event1
01/01/2001 1:00:05 PM Source1 Event1
01/01/2001 1:00:10 PM Source1 Event1
01/01/2001 2:00:00 PM Source1 Event2
01/01/2001 2:00:01 PM Source1 Event2
01/01/2001 2:00:02 PM Source1 Event2
01/01/2001 2:00:03 PM Source1 Event1
would yield only one elligible pair, which is
01/01/2001 1:00:10 PM Source1 Event1
01/01/2001 2:00:00 PM Source1 Event2
hence, the time difference = 3610 seconds
here's the query I came up with, but its really slow.. taking about 2 1/2
minutes to look through 20005 records to find 10000 pairs
SELECT DATEDIFF(ss, Timestamp1, MIN(Timestamp2)) AS secs
FROM (SELECT b.Timestamp AS Timestamp2, MAX(a.Timestamp) AS Timestamp1
FROM tblEvents a INNER JOIN (SELECT *
FROM
tblEvents
WHERE
EventID = 'Event2') b
ON a.ProcessorID = b.ProcessorID
AND a.Timestamp < b.Timestamp
WHERE a.EventID = 'Event1'
GROUP BY b.Timestamp) derivedtbl
GROUP BY Timestamp1
I was wondering if anyone could suggest me a better query. I'm a newbie, so
if somebody could help me I would greatly appreciate it.
Regards,
Ted
>> Stay informed about: Statistical Analysis