如何在SQL Server中基于多条件计算人员不可用时长

需求说明 #

需根据人员状态记录计算不可用时长，规则如下：

场景1 #

当start_date与end_date相同，且status='UNKNOWN'、work_status='ERROR'时，取该记录的end_date到**同人员、当日下一条同状态（UNKNOWN且ERROR）**记录的start_date的时间差。

• 示例：rowid 3和8（person_id=2）：时间差为2023-04-24 10:21:24 - 2023-04-24 10:15:24 = 6分钟
• 若当日存在多次同状态记录，重复此逻辑：如rowid14和15（person_id=2）：时间差为2023-04-24 13:45:29 - 2023-04-24 12:45:29 = 60分钟

场景2 #

若该人员当日无后续UNKNOWN且ERROR状态的记录，则取该人员当日上一条非UNKNOWN状态记录的end_date作为起始时间，计算与当前记录start_date的时间差。

• 示例：person_id=1的rowid13，取rowid11的end_date（2023-04-24 15:12:09）到当前记录start_date（2023-04-24 15:47:00）的时间差为34分51秒

预期结果 #

统计符合上述场景的不可用时长，最终结果需包含人员ID、对应不可用时间段及时长。

测试用SQL脚本 #

CREATE TABLE dbo.human_sample 
(
    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')

内容的提问来源于stack exchange，提问作者kalyan4uonly