代理在上一个呼叫仍在进行时启动一个呼叫

Question

我有一个带有的CALLS表

CALL_ID | OPERATOR_ID |       CALL_START      |        CALL_END       | NOTES | TYPE
========+=============+=======================+=======================+=======+======
101 |    3000     | '2020-12-12 08:00:00' | '2020-12-12 08:10:00' |  ...  | ...
102 |    3000     | '2020-12-12 08:08:00' | '2020-12-12 08:20:00' |  ...  | ...

CALL_ ID 102在CALL_。

我需要找到CALL_END>CALL_START(在同一操作员的下一行(，我被卡住了。

Answer 1

通过OPERATOR_ID连接CALLS表，查找"；右手"；在任意"开始"之间具有call_ START的呼叫记录；左手"；呼叫记录的call_START和call_END：

SELECT c.operator_id,
c.call_id,
c.call_start,
c.call_end,
other.call_id,
other.call_start
FROM calls c
INNER JOIN
calls other
ON c.operator_id = other.operator_id
WHERE c.call_id != other.call_id
AND
other.call_start BETWEEN c.call_start AND c.call_end