Can I subtract 2 call times for every call ID using T-SQL?

Question

I have a table about call information. A call id, call flow such as when they answered, holds, etc, and the time stamps for each flow. I need a code that can search through each call id and run a calculation that returns in a new column, 'Time diff'. For Call ID 1, find Phone and Conference, then subtract the two values(12:08-12:05 = 3 min Not worried about formats now), then go to call ID 2, then 3, and so on. This may be a difficult thing to do in SQL. Note: There is also the possibility that there are more phones in the call, so I would want to subtract the last phone closest to the conference time. This condition can be ignored for now, I really just want to know if there is anyway to subtract conference and phone for now. Thanks.

Call_ID    Flow         Time        Time Diff

1          Inbound      12:00 pm    3
1          Connect      12:01 pm    3
1          Answer       12:01 pm    3
1          Hold         12:04 pm    3
1          Phone        12:05 pm    3
1          Un-hold      12:07 pm    3
1          Conference   12:08 pm    3
1          Call Term    12:20 pm    3
2          Inbound      12:40 pm    2
2          Connect      12:40 pm    2
2          Answer       12:40 pm    2
2          Hold         12:43 pm    2
2          Phone        12:44 pm    2
2          Un-hold      12:46 pm    2
2          Conference   12:46 pm    2
2          Call Term    12:59 pm    2
3
.
.
.

Does this answer your question? [How to get difference between two rows for a column field?](https://stackoverflow.com/questions/634568/how-to-get-difference-between-two-rows-for-a-column-field) — Robert Harvey, Jan 03 '20 at 23:23
Consider providing an SQL Fiddle or DB Fiddle. [Tips for asking a good Structured Query Language question](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question) — Adam Silenko, Jan 08 '20 at 18:45

score 0 · Answer 1 · answered Jan 03 '20 at 23:25

If you want to add a column, you can use window functions:

select t.*,
       datediff(minute,
                min(case when flow = 'phone' then time end) over (partition by call_id),
                min(case when flow = 'conference' then time end) over (partition by call_id)
               ) as timediff_minute
from t;

Adam Silenko · Answer 2 · 2020-01-08T21:12:39.563

0

You can use window functions OVER, or self join...

SELECT Call_ID, Flow, Time
, DATEDIFF(minute
  , Max(CASE WHEN Flow = 'phone' THEN Time END) OVER (PARTITION BY Call_ID)
  , Min(CASE WHEN Flow = 'conference' THEN Time END) OVER (PARTITION BY Call_ID)
  ) Time_Diff
FROM call_information

if you need only conference and last phone time diff, then you can use self join and group by

SELECT conference.Call_ID, conference.Flow, conference.Time
, DATEDIFF(minute, Max(last_phone.Time), conference.Time) Time_Diff
FROM call_information conference
JOIN call_information last_phone 
  ON conference.Call_ID = last_phone.Call_ID
  AND last_phone.Flow = 'phone'
  AND conference.Time > last_phone.Time --if there is posible to have phone after conference
WHERE conference.Flow = 'conference'
GROUP BY conference.Call_ID, conference.Flow, conference.Time

you can also use subquery

EDIT

if you need only sum of Time_Diff for every Call_ID then try this:

SELECT Call_ID, max(conference_time), min(prev_phone_time) 
, Sum(Time_Diff) Total_Time_Diff
FROM
    (SELECT conference.Call_ID, conference.Time conference_time, Max(last_phone.Time) prev_phone_time
    , DATEDIFF(minute, Max(last_phone.Time), conference.Time) Time_Diff
    FROM call_information conference
    JOIN call_information last_phone 
      ON conference.Call_ID = last_phone.Call_ID
      AND last_phone.Flow = 'phone'
      AND conference.Time > last_phone.Time --if there is posible to have phone after conference
    WHERE conference.Flow = 'conference'
    GROUP BY conference.Call_ID, conference.Time)
GROUP BY Call_ID

edited Jan 08 '20 at 21:12

answered Jan 03 '20 at 23:47

Adam Silenko

3,025
1
14
30

Thank you. This worked for the most part. I came across an issue that I didn't know about before. There can be more than one conference in a call. So on those calls, the top code you gave was finding the first conference and last phone which was returning negative values. If a call has more then one conference, then I would probably want to find first conf. and phone closest to that conf., then find next conference and its closest phone, finally add the two time differences together. Note: when I say phone closest to conference, I mean phone right before the conference. – Dylan Jan 08 '20 at 18:15
`SELECT Call_ID, Flow, ???(Time), Sum(Time_Diff)` - what you want to do with time when you have more then one conference? – Adam Silenko Jan 08 '20 at 18:36
Suppose the table above is all call id 1. It has 2 conferences and 2 phones. So if a call id has this case, I would want to find phone closest to first conference, subtract those times, then find next phone closest to the next conference, and add them. The result would look like this. [(12:08-12:05) + (12:46-12:44)] = 3 + 2 = 5. So 5 would be the result (Time Diff) for that call id. – Dylan Jan 08 '20 at 20:19
Are you expect only Call_ID and Sum(Time_Diff) in resoult? – Adam Silenko Jan 08 '20 at 20:23
The sum of the time differences should be in the result for that call id. – Dylan Jan 08 '20 at 20:27

Can I subtract 2 call times for every call ID using T-SQL?

2 Answers2