CODEWITHSUNDEEP
sqlsql-serveroverlapping
dragonfury2
dragonfury2

Reputation: 428

Adding up durations where overlaps present in T SQL

So I have a table which gives me a resulting set with agents and the duration of calls.

I've come a little stuck when overlapping in times occurs. See the following table example:

Agent   |CallType   |StartTime              |EndTime                |Duration(s)    |Number
35171   |3          |06/01/2020 14:12:57    |06/01/2020 14:14:07    |70             |42572491
35171   |3          |06/01/2020 14:23:54    |06/01/2020 14:24:27    |33             |42572491
35171   |3          |06/01/2020 14:34:28    |06/01/2020 14:36:32    |124            |42572491
35171   |3          |06/01/2020 14:43:06    |06/01/2020 14:45:43    |157            |42572491
35171   |4          |06/01/2020 14:45:32    |06/01/2020 15:06:17    |1245           |42572491

I need to get the total duration for this agent which would normally require summing the Duration column. However, if you look at the last row, you can see that the startime starts 11 seconds before the end of the previous call (row above), thus this should not be included in the total(sum) durations. So the overlap should be ommited.

Any directions on how I can achieve this in Sql Server would be great.

Upvotes: 0

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

Hmmm . . . If your overlaps are "simple", you can use a cumulative min() to get the next start time and use that to generate an "adjusted" duration:

select agent,
       sum(case when next_starttime < endtime
                then datediff(second, starttime, next_starttime)
                else duration
           end)
from (select t.*,
             min(starttime) over (partition by agent order by starttime) as next_starttime
      from t
     ) t
group by agent;

By simple, I mean that no call totally "encompasses" another call. So this is allowed:

A------B--A-------B

But not:

A-------B--B------A

The general overlap problem is definitely solvable, but it is rather more complicated. Given that your overlaps are so simple, this should be fine. If you need the more general solution, I would suggest asking a new question with appropriate sample data and desired results.

Upvotes: 1

Related Questions