员工排班中休息日前后连续缺勤的判定计算（MS SQL）

问题需求 #

需实现员工排班系统的连续缺勤判定规则：若员工在排班休息日的前后工作日均缺勤，则该休息日需判定为缺勤。

现有两种排班类型：

• 类型1：周日至周五为工作日，周六为休息日（休息日可调整为任意工作日）
• 类型2：周日至周四为工作日，周五、周六为休息日

判定示例 #

示例1（单休息日排班） #

SUN  P
MON  P
TUE  P
WED  P
THU  A
FRI  A
SAT  Off
SUN  A

结果：周六判定为缺勤（Absent）

示例2（双休息日排班） #

SUN  P
MON  P
TUE  P
WED  P
THU  A
FRI  Off
SAT  Off
SUN  A

结果：周五、周六均判定为缺勤（Absent）

MS SQL 2019数据库环境及测试数据 #

表结构创建语句 #

USE [TEST_DB]
GO
--MS SQL 2019
CREATE TABLE emproster(da_date datetime ,   emp_code varchar(8) ,   shift_status varchar(2) ,   workmin int ) 
Go

测试数据插入语句 #

INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-01','L0094','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-01','T0115','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-02','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-02','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-03','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-03','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-04','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-04','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-05','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-05','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-06','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-06','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-07','L0094','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-07','T0115','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-08','T0115','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-08','L0094','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-09','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-09','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-10','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-10','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-11','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-11','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-12','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-12','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-13','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-13','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-14','T0115','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-14','L0094','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-15','L0094','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-15','T0115','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-16','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-16','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-17','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-17','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-18','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-18','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-19','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-19','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-20','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-20','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-21','L0094','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-21','T0115','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-22','T0115','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-22','L0094','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-23','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-23','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-24','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-24','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-25','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-25','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-26','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-26','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-27','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-27','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-28','T0115','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-28','L0094','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-29','L0094','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-29','T0115','D',0)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-30','T0115','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-30','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-31','L0094','W',570)
INSERT INTO emproster(da_date,emp_code,shift_status,workmin) VALUES ('2022-10-31','T0115','W',570)

字段说明 #

• W: 工作日（Workday）
• D: 休息日（Dayoff）
• P: 出勤（Present）
• A: 缺勤（Absent）

期望输出 #

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