Calculating durations between multiple time stamps

Hi,
I have a problem with calculation call durations in my call logging
database.
My application generates a lock event when a call is opened and an
unlock event when it is put on hold.
These are stored in my CallEvent table (CallID int, EventType
varchar(4), EventComplete DateTime).
There may be multiple lock/unlock events for a call.
Each hour a job runs to populate a reporting table with the duration of
calls.
Currently the job uses the min and max lock/unlock times, this is
incorrect because there may be a substantial delay between lock/unlock
pairs.
I need to generate the call duration using the sum of the times between
lock/unlock pairs.
I don't want to use a cursor for performance reasons, but I can't think
how to calculate the result without putting some kind of loop in.
e.g. Select CallID, EventType, EventComplete
into #CallDuration
from CallEvent
where EventType in ('Unlock', 'Lock')
order by CallID, EventComplete
Then select the top 2 from the temp table, add it to a variable, drop
the 2 rows from the temp table and loop until the callID changes.
Update the reports table and move on to the next CallID.
While this will work it just seems inelegant and possibly slow.
I just can't see how I could sum the durations between lock/unlock pairs.
Assistance appreciated.
MartinUntested:
SELECT CallID, SUM(DATEDIFF(ss, LockStart, LockEnd)) AS TotalCallLength
FROM(
SELECT c1.CallID, c1.EventComplete AS LockStart,
(SELECT MIN(c2.EventComplete) FROM CallEvent c2
WHERE c2.EventType = 'UnLock' AND c2.CallID = c1.CallID
AND c2.EventComplete > c1.EventComplete ) AS LockEnd
FROM CallEvent c1
WHERE c1.EventType = 'Lock'
) a
GROUP BY CallID
Jacco Schalkwijk
SQL Server MVP
"Martin Selway" <mselway@.freeuk.com> wrote in message
news:%23ZUtZTIKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a problem with calculation call durations in my call logging
> database.
> My application generates a lock event when a call is opened and an unlock
> event when it is put on hold.
> These are stored in my CallEvent table (CallID int, EventType varchar(4),
> EventComplete DateTime).
> There may be multiple lock/unlock events for a call.
> Each hour a job runs to populate a reporting table with the duration of
> calls.
> Currently the job uses the min and max lock/unlock times, this is
> incorrect because there may be a substantial delay between lock/unlock
> pairs.
> I need to generate the call duration using the sum of the times between
> lock/unlock pairs.
> I don't want to use a cursor for performance reasons, but I can't think
> how to calculate the result without putting some kind of loop in.
> e.g. Select CallID, EventType, EventComplete
> into #CallDuration
> from CallEvent
> where EventType in ('Unlock', 'Lock')
> order by CallID, EventComplete
> Then select the top 2 from the temp table, add it to a variable, drop the
> 2 rows from the temp table and loop until the callID changes.
> Update the reports table and move on to the next CallID.
> While this will work it just seems inelegant and possibly slow.
> I just can't see how I could sum the durations between lock/unlock pairs.
> Assistance appreciated.
> Martin
>|||Thanks Jacco,
my colleague came up with a similar soln. we'll test it to see if it works.
Martin
Jacco Schalkwijk wrote:
> Untested:
> SELECT CallID, SUM(DATEDIFF(ss, LockStart, LockEnd)) AS TotalCallLength
> FROM(
> SELECT c1.CallID, c1.EventComplete AS LockStart,
> (SELECT MIN(c2.EventComplete) FROM CallEvent c2
> WHERE c2.EventType = 'UnLock' AND c2.CallID = c1.CallID
> AND c2.EventComplete > c1.EventComplete ) AS LockEnd
> FROM CallEvent c1
> WHERE c1.EventType = 'Lock'
> ) a
> GROUP BY CallID
>