SQL - Finding/Matching "pairs" in the same table, by event/date

user_18462647 0
Viewed 124

I'm having issues developing a working solution/query for the case I bring you.

I have a table that receives agent data. What I need is a way to find out the matching "pairs", by day and event, in order to insert them in a temp table, so they can be worked. There can be several entries/pairs in the same day.

A sample of what i'm talking about:

Event Date AGENT Event Type Event Subtype
2022-03-14 09:00 AGENT 1 VOICE CHANNEL LOGIN
2022-03-14 11:10 AGENT 1 BREAK START
2022-03-14 11:20 AGENT 1 BREAK END
2022-03-14 13:10 AGENT 1 VOICE CHANNEL LOGOUT
2022-03-14 14:00 AGENT 1 VOICE CHANNEL LOGIN
2022-03-14 15:50 AGENT 1 BREAK START
2022-03-14 16:00 AGENT 1 BREAK END
2022-03-14 18:10 AGENT 1 VOICE CHANNEL LOGOUT
2022-03-14 10:00 AGENT 2 TICKET CHANNEL LOGIN
2022-03-14 12:00 AGENT 2 BREAK START
2022-03-14 12:10 AGENT 2 BREAK END
2022-03-14 14:00 AGENT 2 TICKET CHANNEL LOGOUT

In this case the 1st agent 1 'voice channel'+'login' should be paired with the first agent 1 'voice channel'+'logout', the first 'break'+'start' should be paired with the first 'break'+'end', the second agent 1 'voice channel'+'login' with the second agent 1 'voice channel'+'logout' and the second 'break'+'start' with the second 'break'+'end', and so forth.

The destination temp table will have the columns 'Agent', 'event', 'event start' and 'event end'.

@Coder1991 The final temp table should read something like this:

AGENT Event Type Event Start Event End
AGENT 1 VOICE CHANNEL 2022-03-14 09:00 2022-03-14 13:00
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:00
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

Any suggestions / inputs are appreciated.

Thank you all in advance, and have a great week.

sql-server sql
2 Answers

You can use a Gaps-And-Islands trick for this.

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

Once you have the rank, it just a matter of aggregation.

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

Demo on db<>fiddle here

user_40034190

Please try the below approach

 declare @tbl table([Event Date] datetime,AGENT varchar(10),[Event Type] 
 varchar(20)
,[Event Subtype] varchar(20)
 )

insert into @tbl
values('2022-03-14 09:00','AGENT 1','VOICE CHANNEL','LOGIN')
,('2022-03-14 11:10','AGENT 1','BREAK','START')
,('2022-03-14 11:20','AGENT 1','BREAK','END')
,('2022-03-14 13:10','AGENT 1','VOICE CHANNEL','LOGOUT')
,('2022-03-14 14:00','AGENT 1','VOICE CHANNEL','LOGIN')
,('2022-03-14 15:50','AGENT 1','BREAK','START')
,('2022-03-14 16:00','AGENT 1','BREAK','END')
,('2022-03-14 18:10','AGENT 1','VOICE CHANNEL','LOGOUT')
,('2022-03-14 19:00','AGENT 1','VOICE CHANNEL','LOGIN')
,('2022-03-14 20:10','AGENT 1','VOICE CHANNEL','LOGOUT')
,('2022-03-14 10:00','AGENT 2','TICKET CHANNEL','LOGIN')
,('2022-03-14 12:00','AGENT 2','BREAK','START')
,('2022-03-14 12:10','AGENT 2','BREAK','END')
,('2022-03-14 14:00','AGENT 2','TICKET CHANNEL','LOGOUT')


select *
, case when ROW_NUMBER()over(partition by agent,[Event Type] order by [Event Date]) % 2
 != 0 then [Event Date] else null end 
[Event Start]
,case when ROW_NUMBER()over(partition by agent,[Event Type] order by [Event Date]) % 2
 = 0 then [Event Date] else null end 
[Event End]
into #temp
from @tbl

;with cte as(
select AGENT,[Event Type],[Event Start]
,lead([Event End],1)over(partition by agent,[Event Type] order by [Event Date]) [Event End]
from
#temp
)
select *
from cte 
where [Event Start] is not null
order by AGENT,[Event Start] 

drop table #temp
user_52137790
Related