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
| User_Id | TotalLoginTime | TotalIdleTime | ServiceLoginTime | ServiceIdleTime |
|---|---|---|---|---|
| s110047 | 720 | 90 | 900 | 20 |