SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE AgentActivitySummary
	([BeginTimePeriodDt] datetime, [User_id] varchar(7), [Service_Id] int, [LoginDt] datetime, [WorkGroup_Id] int, [EndTimePeriodDt] varchar(23)
	, [Site_Id] int, [TenantId] int, [TotalLoginTime] int, [TotalIdleTime] int);
	
INSERT INTO AgentActivitySummary
	([BeginTimePeriodDt], [User_id], [Service_Id], [LoginDt], [WorkGroup_Id], [EndTimePeriodDt], [Site_Id], [TenantId], [TotalLoginTime], [TotalIdleTime])
VALUES
('2018-10-02 09:30:00', 's110047', 0, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 90),
('2018-10-02 09:30:00', 's110047', 53000462, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 0),
('2018-10-02 09:30:00', 's110047', 53000485, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 900, 0),
('2018-10-02 09:30:00', 's110047', 53000486, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 900, 0),
('2018-10-02 09:30:00', 's110047', 53000487, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 900, 0),
('2018-10-02 09:30:00', 's110047', 53000577, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 20)
;
6 rows affected

select *
from AgentActivitySummary


BeginTimePeriodDt
User_id
Service_Id
LoginDt
WorkGroup_Id
EndTimePeriodDt
Site_Id
TenantId
TotalLoginTime
TotalIdleTime


2018-10-02 09:30:00.000
s110047
0
2018-10-02 09:32:59.000
53000132
2018-10-02 09:45:00.000
54
1
720
90

2018-10-02 09:30:00.000
s110047
53000462
2018-10-02 09:32:59.000
53000132
2018-10-02 09:45:00.000
54
1
720
0

2018-10-02 09:30:00.000
s110047
53000485
2018-10-02 09:32:59.000
53000132
2018-10-02 09:45:00.000
54
1
900
0

2018-10-02 09:30:00.000
s110047
53000486
2018-10-02 09:32:59.000
53000132
2018-10-02 09:45:00.000
54
1
900
0

2018-10-02 09:30:00.000
s110047
53000487
2018-10-02 09:32:59.000
53000132
2018-10-02 09:45:00.000
54
1
900
0

2018-10-02 09:30:00.000
s110047
53000577
2018-10-02 09:32:59.000
53000132
2018-10-02 09:45:00.000
54
1
720
20


select distinct V0.User_Id,
     V1.TotalLoginTime,V2.TotalIdleTime,
     V3.ServiceLoginTime,V4.ServiceIdleTime
from AgentActivitySummary as V0     
-- A.Service_Id='0' & TotalLoginTime
left join 
(
select K1.User_Id,
sum(K1.TotalLoginTime) as TotalLoginTime
from (
select 
A.User_Id,A.BeginTimePeriodDt,
max(TotalLoginTime) as TotalLoginTime
from AgentActivitySummary as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-01' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id='0'
group by A.User_Id,A.BeginTimePeriodDt
) AS K1
where 1=1
Group by K1.User_Id
) as V1 on V1.User_Id=V0.User_Id
-- A.Service_Id='0' & TotalIdleTime
left join
(
select K2.User_Id,
sum(K2.TotalIdleTime) as TotalIdleTime
from (
select 
A.User_Id,A.BeginTimePeriodDt,
max(TotalIdleTime) as TotalIdleTime
from AgentActivitySummary as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-01' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id='0'
group by A.User_Id,A.BeginTimePeriodDt
) AS K2
where 1=1
Group by K2.User_Id
) as V2 on V2.User_Id=V0.User_Id
-- A.Service_Id= other & ServiceLoginTime
left join 
(
select K3.User_Id,
sum(K3.TotalLoginTime) as ServiceLoginTime
from (
select 
A.User_Id,A.BeginTimePeriodDt,
max(TotalLoginTime) as TotalLoginTime
from AgentActivitySummary as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-01' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id not in ('0','53000462','53000566','53000567','53000485')
group by A.User_Id,A.BeginTimePeriodDt
) AS K3
where 1=1
Group by K3.User_Id
) V3 on V3.User_Id=V0.User_Id
-- A.Service_Id= other & ServiceIdleTime
left join
(
select K4.User_Id,
sum(K4.TotalIdleTime) as ServiceIdleTime
from (
select 
A.User_Id,A.BeginTimePeriodDt,
max(TotalIdleTime) as TotalIdleTime
from AgentActivitySummary as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-01' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id not in ('0','53000462','53000566','53000567','53000485')
group by A.User_Id,A.BeginTimePeriodDt
) AS K4
where 1=1
Group by K4.User_Id
) V4 on V4.User_Id=V0.User_Id
order by V0.User_Id


User_Id
TotalLoginTime
TotalIdleTime
ServiceLoginTime
ServiceIdleTime


s110047
720
90
900
20

BeginTimePeriodDt	User_id	Service_Id	LoginDt	WorkGroup_Id	EndTimePeriodDt	Site_Id	TenantId	TotalLoginTime	TotalIdleTime
2018-10-02 09:30:00.000	s110047	0	2018-10-02 09:32:59.000	53000132	2018-10-02 09:45:00.000	54	1	720	90
2018-10-02 09:30:00.000	s110047	53000462	2018-10-02 09:32:59.000	53000132	2018-10-02 09:45:00.000	54	1	720	0
2018-10-02 09:30:00.000	s110047	53000485	2018-10-02 09:32:59.000	53000132	2018-10-02 09:45:00.000	54	1	900	0
2018-10-02 09:30:00.000	s110047	53000486	2018-10-02 09:32:59.000	53000132	2018-10-02 09:45:00.000	54	1	900	0
2018-10-02 09:30:00.000	s110047	53000487	2018-10-02 09:32:59.000	53000132	2018-10-02 09:45:00.000	54	1	900	0
2018-10-02 09:30:00.000	s110047	53000577	2018-10-02 09:32:59.000	53000132	2018-10-02 09:45:00.000	54	1	720	20