Retrieve the timediff from a timestamp field of the current row and the same field of the next row in my subquery

Question

I want to retrieve the timediff from a timestamp field of the current row and the same field of the next row in my subquery. But only where the user is the same and the timediff is greter than 10 min. The return value is grouped by day. Why is the returned value much higher than 60hours which cannot be right since a day only have 24 hours. What am i missing?

Please help:

select DATE_FORMAT( last_call, '%d' ) AS 'day',
    COUNT(call_id) as id,

   SEC_TO_TIME(SUM((select timestampdiff(second, calls.last_call, c2.last_call)
    from calls c2
    where c2.calling_agent = calls.calling_agent and
          c2.last_call > calls.last_call and
          timestampdiff(second, calls.last_call, c2.last_call) > 600 and
          c2.last_call
    order by c2.last_call
    limit 1
   ))) brakes from calls WHERE calling_agent =9  AND 
last_call > DATE_SUB( now( ) , INTERVAL 12 MONTH ) 
GROUP BY EXTRACT( DAY FROM last_call )

The agent made multiple calls, so there are multiple values being added together. — Gordon Linoff, Jan 11 '15 at 20:24
yes but i only want to summaries the timediff between calls when the timediff are greather than 10min. and only the timediff between the first and the last call of the day, can you help please? — jhon dano, Jan 11 '15 at 20:29

score 0 · Answer 1 · edited May 23 '17 at 11:49

The problem is in the nested query that calculates the time difference.

Let's create a sample table to explain:

create table calls (call_id int, calling_agent int, last_call datetime);
insert into calls VALUES (1, 9, "2015-01-26 14:00:00");
insert into calls VALUES (2, 9, "2015-01-26 14:05:00");
insert into calls VALUES(3, 9, "2015-01-26 14:12:00");
select * from calls;

| CALL_ID | CALLING_AGENT |                      LAST_CALL |
|---------|---------------|--------------------------------|
|       1 |             9 | January, 26 2015 14:00:00+0000 |
|       2 |             9 | January, 26 2015 14:05:00+0000 |
|       3 |             9 | January, 26 2015 14:12:00+0000 |

If you see the data, both the breaks were less than 10 minutes (5 minutes and 7 minutes). So the sum should be 0. But your query will join row 1 and 3 and calculate the break time as 12.

select DATE_FORMAT( last_call, '%d' ) AS 'day',
    COUNT(call_id) as id,

   SEC_TO_TIME(SUM((select timestampdiff(second, calls.last_call, c2.last_call)
    from calls c2
    where c2.calling_agent = calls.calling_agent and
          c2.last_call > calls.last_call and
          timestampdiff(second, calls.last_call, c2.last_call) > 600 and
          c2.last_call
    order by c2.last_call
    limit 1
   ))) brakes from calls WHERE calling_agent =9  AND 
last_call > DATE_SUB( now( ) , INTERVAL 12 MONTH ) 
GROUP BY EXTRACT( DAY FROM last_call );

| DAY | ID |    BRAKES |
|-----|----|-----------|
|  26 |  3 |  00:12:00 |

You can play with this here.

Because of this error the sum of all breaks can exceed 24 hours too. I created an example here.

To actually solve this problem, I would suggest creating a temporary table with ranks as explained here.

CREATE TEMPORARY TABLE c1
SELECT    call_id, calling_agent, last_call,
          @curRank := @curRank + 1 AS rank
FROM      calls, (SELECT @curRank := 0) r
ORDER BY last_call;

Then you can use this query.

CREATE TEMPORARY TABLE c2 SELECT * FROM c1; -- create copy because sqlize doesn't allow joins with the same table.

SELECT
    DATE_FORMAT( t.last_call, '%d' ) AS 'day',
    COUNT(t.call_id) as id,
    SEC_TO_TIME(SUM(t.BreakTimeInSeconds)) AS TotalBreakTime
FROM
(
    SELECT
        c1.call_id, c1.calling_agent, c1.last_call, TIMESTAMPDIFF(SECOND, c1.last_call, c2.last_call) AS BreakTimeInSeconds
    FROM c1,c2
    WHERE c1.calling_agent = c2.calling_agent
    AND c1.rank + 1 = c2.rank
    HAVING BreakTimeInSeconds > 600
) AS t
WHERE t.calling_agent = 9 AND 
    t.last_call > DATE_SUB( now( ) , INTERVAL 12 MONTH ) 
GROUP BY EXTRACT( DAY FROM t.last_call );

Run it here.

You can do this without a temporary table too, but the query might become too complex.

The example I [linked](http://sqlize.com/K361QidAs2) to at the end of the post show that it works for that sample data. I suggest you edit your question with more details (schema of your table, some sample data, the exact query used, the output, etc.) — Turbo, Jan 27 '15 at 23:10

Retrieve the timediff from a timestamp field of the current row and the same field of the next row in my subquery

1 Answers1