Всем привет!
Пытаюсь сделать выборку по перезвонам на непринятые звонки с вычитанием времени перезвона.
Сделал два похожих запроса:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT from_number, call_create_time, outgoing.operator_id_outgoing, MIN(outgoing.cct_outgoing - client_calls.call_create_time) as sub
from client_calls
JOIN
(SELECT from_number as fn_outgoing,
call_create_time as cct_outgoing,
operator_id as operator_id_outgoing
from client_calls
WHERE type = 'OUTGOING' AND date(`created_at`) = '2019-07-27') as outgoing
ON client_calls.from_number = outgoing.fn_outgoing AND client_calls.call_create_time < outgoing.cct_outgoing
WHERE type = 'INCOMING' AND status_call = 0 AND date(`created_at`) = '2019-07-27'
GROUP BY from_number, call_create_time, outgoing.operator_id_outgoing
ORDER BY from_number, sub
1.
2.
3.
4.
5.
6.
7.
SELECT *, (cct_outgoing - missed.call_create_time) as sub FROM
(SELECT from_number, call_create_time from client_calls WHERE type = 'INCOMING' AND status_call = 0 AND date(`created_at`) = '2019-07-27') as missed
JOIN
(SELECT from_number as fn_outgoing, call_create_time as cct_outgoing, operator_id as operator_id_outgoing from client_calls WHERE type = 'OUTGOING' AND date(`created_at`) = '2019-07-27') as outgoing
ON missed.from_number = outgoing.fn_outgoing AND missed.call_create_time < outgoing.cct_outgoing
ORDER BY from_number, call_create_time
Получаю результат, который нужно отфильтровать по номеру, времени звонка и минимальному времени перезвона. GROUP BY могу воспользоваться, но мне еще нужен operator_id.....
Помогите плиз с решением
