SQL 按eventdate在同一个表中FindingMatching pairs

你可以使用一个缺口和岛屿的技巧.

A ranking can be calculated via using a SUM OVER a flag.
And the flag is the start of the types per agent.

一旦你有了排名，这只是一个聚合的问题.

SELECT AGENT, [Event Type]
, MIN(CASE WHEN [Event SubType] IN ('LOGIN', 'START') THEN [Event Date] END) AS [Event Start] 
, MAX(CASE WHEN [Event SubType] IN ('LOGOUT', 'END')  THEN [Event Date] END) AS [Event End] 
FROM
(
SELECT [Event Date], AGENT, [Event Type], [Event Subtype]
, Rnk = SUM(IIF([Event SubType] IN ('LOGIN', 'START'), 1, 0)) 
        OVER (PARTITION BY AGENT, [Event Type], CAST([Event Date] AS DATE) ORDER BY [Event DATE])
FROM agent_events 
) q
GROUP BY AGENT, [Event Type], CAST([Event Date] AS DATE), Rnk
ORDER BY AGENT, [Event Start];
GO

AGENT   | Event Type     | Event Start      | Event End       
:------ | :------------- | :--------------- | :---------------
AGENT 1 | VOICE CHANNEL  | 2022-03-14 09:00 | 2022-03-14 13:10
AGENT 1 | BREAK          | 2022-03-14 11:10 | 2022-03-14 11:20
AGENT 1 | VOICE CHANNEL  | 2022-03-14 14:00 | 2022-03-14 18:10
AGENT 1 | BREAK          | 2022-03-14 15:50 | 2022-03-14 16:00
AGENT 2 | TICKET CHANNEL | 2022-03-14 10:00 | 2022-03-14 14:00
AGENT 2 | BREAK          | 2022-03-14 12:00 | 2022-03-14 12:10

db<>fiddle 100演示