CODEWITHSUNDEEP
mysqlsqldatejoinmin
harp1814
harp1814

Reputation: 1658

Create query to get count of uncompleted calls group by 2 fields

A small update of this question Create query to get count of uncompleted calls

There is table waiter_log as

+---------------------+---------+----------------+--------------+--------------+
| call_time           | call_id | queue_num_curr | ast_num_curr | proceed_wait |
+---------------------+---------+----------------+--------------+--------------+
| 2019-11-18 08:14:30 | f27de4f | 9010           | 2            |            1 |
| 2019-11-18 08:14:35 | f27de4f | 9002           | 5            |            1 |
| 2019-11-18 08:14:41 | f27de4f | 9003           | 1            |            0 |
| 2019-11-18 08:14:45 | asdf231 | 9010           | 2            |            1 |
| 2019-11-18 08:14:50 | asdf231 | 9002           | 5            |            1 |
| 2019-11-18 08:14:55 | rete125 | 9010           | 2            |            1 |
| 2019-11-18 08:15:00 | rete125 | 9009           | 5            |            1 |
| 2019-11-18 08:15:05 | a7rf5gs | 9003           | 2            |            1 |
| 2019-11-18 08:15:10 | a7rf5gs | 9006           | 5            |            1 |
| 2019-11-18 08:15:15 | a7rf5gs | 9009           | 1            |            0 |
| 2019-11-18 08:15:20 | qawe234 | 9003           | 2            |            1 |
| 2019-11-18 08:15:25 | qawe234 | 9008           | 5            |            1 |
| 2019-11-18 08:15:30 | qawe234 | 9004           | 1            |            0 |
| 2019-11-18 08:15:35 | 49c43ad | 9004           | 2            |            1 |
| 2019-11-18 08:15:41 | 49c43ad | 9007           | 5            |            1 |
| 2019-11-18 08:15:45 | bxfdrtr | 9010           | 3            |            1 |
| 2019-11-18 08:15:50 | bxfdrtr | 9012           | 4            |            1 |
| 2019-11-18 08:15:55 | tofnt62 | 9010           | 5            |            1 |
| 2019-11-18 08:16:00 | tofnt62 | 9021           | 1            |            1 |
+---------------------+---------+----------------+--------------+--------------+

Call with call-id 'f27de4f' started in 9010 and finished in 9003 because there is a record with proceed_wait = 0 for call-id='f27de4f' Call with call-id 'asdf231' started in 9010, still proceed in 9002 and not finished yet because there is no record with proceed_wait = 0 for call-id='asdf231' Similarly for call with call-id 'rete125' there is no record with proceed_wait = 0 and this call is not completed too. So,for queue 9010 query result should be:

queue_num      ast_num  count 
9010            2       2
9010            3       1
9010            5       1

For 9003 result should be 0 , because all calls for 9003 ('a7rf5gs' and 'qawe234') are completed. For 9004 result should be 1 because there is no record with proceed_wait = 0 for call with call-id '49c43ad'.

So result should be:

queue_num      ast_num  count 
9010            2       2
9010            3       1
9010            5       1
9004            2       1

Upvotes: 1

Views: 59

Answers (1)

GMB
GMB

Reputation: 222592

You could join the table with an aggregate query that retrieves the minimum call_time par call_id of unfinished calls. A unfinished call is a call that has no record where proceed_wait = 0.

select t.queue_num_curr, t.ast_num_curr, count(*)
from mytable t
inner join (
    select call_id, min(call_time) call_time
    from mytable 
    group by call_id
    having max(proceed_wait = 0) = 0
) tmin on tmin.call_id = t.call_id and tmin.call_time = t.call_time
group by t.queue_num_curr, t.ast_num_curr
order by t.queue_num_curr, t.ast_num_curr

Demo on DB Fiddle:

queue_num_curr | ast_num_curr | count(*)
-------------: | -----------: | -------:
          9004 |            2 |        1
          9010 |            2 |        2
          9010 |            3 |        1
          9010 |            5 |        1

NB: I think that in the results, queue_num = 9004 should have ast_num = 2 instead of 1 (that should correspond to call_id 49c43ad).

Upvotes: 1

Related Questions