Agent Login Logout report

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)



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


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

Kind regards


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 
        (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
  , max(login)
  , max(logout) 
group by