Agent Login Logout report

Transact-SQL
1

Hi everyone, I would like to create an agent login/logout report and make it nice and easy to read. The table I'm using has the login & logout times on separate rows (see example 1), but I would like to get each login and logout on the same row, preferably with the duration, but I'm stumped on how I can do this. If the login and logout data were on the same rows I could probably use some kind of case statement, but over multiple rows I'm lost. Could someone help?

Example 1 (Table)

Table

Code

select (b.Firstname + ' ' + b.Lastname) as AgentName, AgentID, agenttimeutc,
convert(varchar, CAST(agenttimeutc AT TIME ZONE 'UTC' AT TIME ZONE 'GMT standard time' as datetime), 103) as Date,
convert(varchar(5), CAST(agenttimeutc AT TIME ZONE 'UTC' AT TIME ZONE 'GMT standard time' as datetime), 8) as Time,
propertyname, propertyvalue
from [I3_IC].[ININ_DIALER_40].[AgentStats] a
Inner Join [dbo].[Individual] b On a.agentid = b.ICUserID
Where datediff(day,agenttimeUTC,getdate()) = 1 and campaignname = 'GBR Test' and propertyname in ('Login', 'Logout')
order by Agentid, agenttimeUTC asc

Example output

Output

Thank you for taking the time to look at my problem.

Kind regards

2

hi

hope this helps

create data script

drop table #Data

create table #Data ( id int identity(1,1),AgentName varchar(20) , AgentID varchar(20) , AgentTimeUTC datetime , Dt date , Tme time , PropertyName varchar(10))

insert into #Data select 'Wendy Bentham','benthwe','2023-09-19 14:00:22' , '2023-09-19','15:00','Login'
insert into #Data select 'Wendy Bentham','benthwe','2023-09-19 17:27:04' , '2023-09-19','18:27','LogOut'

insert into #Data select 'Wendy Bentham','benthwe','2023-09-19 17:34:22' , '2023-09-19','18:34','Login'
insert into #Data select 'Wendy Bentham','benthwe','2023-09-19 19:01:04' , '2023-09-19','20:01','LogOut'

insert into #Data select 'Clair Catling','caitcl','2023-09-19 07:34:22' , '2023-09-19','08:00','Login'
insert into #Data select 'Clair Catling','caitcl','2023-09-19 15:46:04' , '2023-09-19','16:46','LogOut'

 ; with cte as 
(
select 
        (row_number() over(partition by agentname order by dt,tme) -1)/2 as rn
      , agentname 
      , case when propertyname = 'login'  then tme end  as login 
	  , case when propertyname = 'logout' then tme end  as logout
from 
   #data
)
select 
  agentname
  , max(login)
  , max(logout) 
from 
   cte 
group by 
     rn 
	 ,agentname

image