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:

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:

Any suggestions / inputs are appreciated.

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

CodePudding user response：

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

CodePudding user response：

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