There is table waiter_log as
+---------+----------------+--------------+--------------+ | call_id | queue_num_curr | ast_num_curr | proceed_wait | +---------+----------------+--------------+--------------+ | f27de4f | 9010 | 2 | 1 | | f27de4f | 9002 | 5 | 1 | | f27de4f | 9003 | 1 | 0 | | asdf231 | 9010 | 2 | 1 | | asdf231 | 9002 | 5 | 1 | | rete125 | 9010 | 2 | 1 | | rete125 | 9009 | 5 | 1 | | a7rf5gs | 9003 | 2 | 1 | | a7rf5gs | 9006 | 5 | 1 | | a7rf5gs | 9009 | 1 | 0 | | qawe234 | 9003 | 2 | 1 | | qawe234 | 9008 | 5 | 1 | | qawe234 | 9004 | 1 | 0 | | 49c43ad | 9004 | 2 | 1 | | 49c43ad | 9007 | 5 | 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 2 (count of uncompleted calls)
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’.
How to create a query to get count on uncompleted calls as:
queue_num count 9010 2 9004 1
UPD: Here i updated my question Create query to get count of uncompleted calls group by 2 fields
Advertisement
Answer
Here’s another method that works without correlated subqueries or window functions:
For each row w1
, try to find another row w2
with the same call_id and a 0 indicating the call is complete. Using a LEFT OUTER JOIN, we can test for cases where no w2
row exists for a given call_id.
Then do another join to a hypothetical row w3
with the same call_id and a lesser ast_num_curr value. Again, using outer join, we can check that no such row exists. This means w1
must have the least value for ast num for that call_id.
SELECT w1.call_id, w1.queue_num_curr FROM waiter_log AS w1 LEFT OUTER JOIN waiter_log AS w2 ON w1.call_id = w2.call_id AND w2.proceed_wait = 0 LEFT OUTER JOIN waiter_log AS w3 ON w1.call_id = w3.call_id AND w1.ast_num_curr > w3.ast_num_curr WHERE w2.call_id IS NULL AND w3.call_id IS NULL;
Output:
+---------+----------------+ | call_id | queue_num_curr | +---------+----------------+ | 49c43ad | 9004 | | asdf231 | 9010 | | rete125 | 9010 | +---------+----------------+
To get the counts per queue_num_curr, wrap the query above in a derived-table subquery, and do the count in the outer query:
SELECT queue_num_curr, COUNT(*) AS count FROM ( SELECT w1.call_id, w1.queue_num_curr FROM waiter_log AS w1 LEFT OUTER JOIN waiter_log AS w2 ON w1.call_id = w2.call_id AND w2.proceed_wait = 0 LEFT OUTER JOIN waiter_log AS w3 ON w1.call_id = w3.call_id AND w1.ast_num_curr > w3.ast_num_curr WHERE w2.call_id IS NULL AND w3.call_id IS NULL ) AS t GROUP BY queue_num_curr;
Output:
+----------------+-------+ | queue_num_curr | count | +----------------+-------+ | 9004 | 1 | | 9010 | 2 | +----------------+-------+