(

rowid int,

person_id int,

start_date datetime,

end_date datetime,

status varchar(100),

work_status varchar(100)

);

INSERT INTO dbo.human_sample VALUES (1, 1, '2023-04-24 10:10:15', '2023-04-24 10:15:24', 'IDLE', 'OK')

INSERT INTO dbo.human_sample VALUES (2, 1, '2023-04-24 10:15:24' ,'2023-04-24 10:19:45', 'IDLE', 'OK')

INSERT INTO dbo.human_sample VALUES (3, 2, '2023-04-24 10:15:24', '2023-04-24 10:15:24', 'UNKNOWN', 'ERROR')

INSERT INTO dbo.human_sample VALUES (4, 1, '2023-04-24 10:45:45', '2023-04-24 11:35:21', 'IDLE', 'OK')

INSERT INTO dbo.human_sample VALUES (5, 1, '2023-04-24 11:35:21', '2023-04-24 11:35:21', 'IDLE', 'OK')

INSERT INTO dbo.human_sample VALUES (6, 1, '2023-04-24 11:35:21', '2023-04-24 11:55:41', 'IDLE', 'OK')

INSERT INTO dbo.human_sample VALUES (7, 1, '2023-04-24 11:55:41', '2023-04-24 13:25:11', 'ACTIVE', 'OK')

INSERT INTO dbo.human_sample VALUES (8, 2, '2023-04-24 10:21:24', '2023-04-24 10:21:24', 'UNKNOWN', 'ERROR')

INSERT INTO dbo.human_sample VALUES (9, 2, '2023-04-24 10:21:24', '2023-04-24 11:45:49', 'IDLE', 'OK')

INSERT INTO dbo.human_sample VALUES (10, 1, '2023-04-24 13:25:11', '2023-04-24 14:12:41', 'ACTIVE', 'OK')

INSERT INTO dbo.human_sample VALUES (11, 1, '2023-04-24 14:12:41', '2023-04-24 15:12:09', 'IDLE', 'OK')

INSERT INTO dbo.human_sample VALUES (12, 2, '2023-04-24 11:45:49', '2023-04-24 12:45:29', 'IDLE', 'OK')

INSERT INTO dbo.human_sample VALUES (13, 1, '2023-04-24 15:47:00', '2023-04-24 15:47:00', 'UNKNOWN', 'ERROR')

INSERT INTO dbo.human_sample VALUES (14, 2, '2023-04-24 12:45:29', '2023-04-24 12:45:29', 'UNKNOWN', 'ERROR')

INSERT INTO dbo.human_sample VALUES (15, 2, '2023-04-24 13:45:29', '2023-04-24 13:45:29', 'UNKNOWN', 'ERROR')

cte as

(

select rowid, person_id, start_date, end_date, status, work_status,

[date] = convert(date, start_date),

case_flag = case when sum (case when status = 'UNKNOWN' then 1 else 0 end)

over (partition by person_id, convert(date, start_date))

> 1

then 1

else 2

end

from human_sample

),

case_1_cte as

(

select person_id, [date],

rn = row_number() over (partition by person_id, [date] order by start_date),

start_date, end_date

from cte

where case_flag = 1

and status = 'UNKNOWN'

),

case_1 as

(

select person_id, [date],

Unavailable_time_in_seconds = datediff(second, min(end_date), max(start_date))

from case_1_cte

group by person_id, [date], (rn - 1) / 2

),

case_2_cte as

(

select person_id, [date], start_date, end_date, status,

prev_end_date = lag(end_date) over (partition by person_id, [date]

order by start_date)