Sum variable amount of intervals together

Question

we just changed our telephony system and every agents are now being logged through 15 minute intervals and we need 1 line per event

table event:

empid  | code  |   timestamp  |   duration
5111   |   5   |   09:45:00   |   45
5222   |   2   |   09:58:00   |   120
5111   |   5   |   10:00:00   |   900
5111   |   5   |   10:15:00   |   900
5111   |   5   |   10:15:30   |   30    
5222   |   5   |   11:00:00   |   8   
5222   |   5   |   11:00:05   |   5   

timestamp is writen after the fact, so a timestamp at 9:45:00 with a duration of 45 was from 9:44:15 and since the interval stopped at 9:45, it was written at that time, but i need 9:44:15 save

result should give me

empid | code |  timestamp |  duration
5111  |  5   |  09:44:15  |  1875
5222  |  2   |  09:56:00  |  120
5222  |  5   |  10:59:52  |  13

The problem is the phones are locked with a 2 hours max delay, and as you can see with my employee # 5222 he spent 13 seconds on two lines... i could join the same table 10 times. 1 to avoid when there is the same code where the end time of the previous line = the starttime of the new line

this is on MSSQL 2008

Select e.empid
            ,e.code
            ,convert(time(0),DATEADD(ss,- e.Duration, e.timestamp))
            ,e.duration + isnull(e1.duration,0) + isnull(e2.duration,0)

    from [event] e
left join [event] e0 on 
convert(TIME(0),DATEADD(ss,- e.Duration, e.timestamp)) = e0.timestamp
and 
e.empid = e0.empid
and 
e.code = e0.code

left join [event] e1 on 
convert(TIME(0),DATEADD(ss,- e1.Duration, e1.timestamp)) = e.timestamp
and 
e.empid = e1.empid
and 
e.code = e1.code



left join [event] e2 on 
convert(TIME(0),DATEADD(ss,- e2.Duration, e2.timestamp)) = e1.timestamp  
and 
e2.empid = e1.empid
and 
e2.code = e1.code

--etc......  

where isnull(e0.duration,'-10') = '-10'

This works but far from optimal...

i would rather use an aggregate function but i dont know how to write it as there is no comon key other than last timestamps match with new - duration with this table!

it is important to know that agent 5111 could go again on code 5 on the same day, and i would need 2 lines for this one.... if not it would have been too easy!

thank you in advance!

Answer 1

Try this. I have commented in the code, but the basic algorithm

1. find rowswhich are continuations i.e. there exists a row which matches once you subtract the duration
2. find the "originals" i.e. the start of each call by subtracting the continuations
3. for each original, find the next original so we can determine a range of times to look for continuations
4. join it all together and add the total duration from continuations appropriate to each original

Hope this helps, it was an interesting challenge!

---

declare @data table
(
    empid int,
    code int,
    [timestamp] time,
    duration int
);

insert into @data values(5111,5,'09:45',45),
(5222,2,'09:58',120),
(5111,5,'10:00',900),
(5111,5,'10:15',900),
(5111,5,'10:15:30',30),
(5222,5,'11:00',8),
(5222,5,'11:00:05',5),
-- added these rows to include the situation you describe where 5111 goes again on code 5:
(5111,5,'13:00',45),
(5111,5,'13:15',900),
(5111,5,'13:15:25',25);

-- find where a row is a continuation 
with continuations as (
    select a.empid, a.code, a.[timestamp] , a.duration
    from @data a 
    inner join @data b on a.empid = b.empid 
        and a.code = b.code
    where dateadd(ss, -a.duration, a.[timestamp]) = b.[timestamp]
),
-- find the "original" rows as the complement of continuations
originals as
(
    select d.empid, d.code, d.[timestamp], d.duration
    from @data d
    left outer join continuations c on d.empid = c.empid and d.code = c.code and d.timestamp = c.timestamp
    where c.empid is null
),
-- to hand the situation where we have more than one call for same agent and code, 
-- find the next timestamp for each empid/code 
nextcall as (
    select a.*, a2.[timestamp] nex
    from originals a
    outer apply (
        select top 1 [timestamp]
        from originals a2
        where a2.[timestamp] > a.[timestamp]
            and a.empid = a2.empid
            and a.code = a2.code
        order by a2.[timestamp] desc
    ) a2
)
select o.empid,
    o.code,
    dateadd(ss, -o.duration, o.timestamp) as [timestamp],
    o.duration + isnull(sum(c.duration),0) as duration
from originals o
left outer join nextcall n on o.empid = n.empid and o.code = n.code and o.[timestamp] = n.[timestamp] 
left outer join continuations c on o.empid = c.empid
    and o.code = c.code
    -- filter the continuations on the range of times based on finding the next one
    and c.[timestamp] > o.[timestamp]
    and (n.nex is null or c.[timestamp] < n.nex) 
group by o.empid,
    o.code,
    o.duration,
    o.[timestamp]