跨24小时周期的员工班次起始日期统一标记需求

需要筛选出在**指定24小时周期（从某日08:00至次日08:00）**内工作的所有员工，无论其班次实际起止时间如何，均新增一列将这些员工的班次起始日统一标记为该24小时周期的起始日期。

示例 #

如下表中所有员工的班次均处于7/26/22 08:00至7/27/22 08:00的24小时周期内，需新增班次日列显示为7/26/22：

解决方案 #

1. SQL实现 #

假设表名为employee_shifts，日期时间字段为start_time和end_time，指定周期的起始时间为'2022-07-26 08:00:00'，结束时间为'2022-07-27 08:00:00'：

SELECT 
    first,
    start_time,
    end_time,
    hours,
    '2022-07-26' AS 班次日
FROM employee_shifts
WHERE 
    -- 筛选班次与指定周期有重叠的员工
    start_time < '2022-07-27 08:00:00'
    AND end_time > '2022-07-26 08:00:00';

如果需要动态适配任意日期的周期，可通过日期函数自动生成时间范围：

-- 设定目标基准日期
SET @target_date = '2022-07-26';
SET @period_start = CONCAT(@target_date, ' 08:00:00');
SET @period_end = DATE_ADD(@period_start, INTERVAL 24 HOUR);

SELECT 
    first,
    start_time,
    end_time,
    hours,
    @target_date AS 班次日
FROM employee_shifts
WHERE 
    start_time < @period_end
    AND end_time > @period_start;

2. Excel实现 #

假设数据在A-D列，指定周期起始时间为7/26/22 08:00：

• 新增E列（班次日），输入公式并下拉填充：

  =IF(AND(B2<"7/27/22 08:00", C2>"7/26/22 08:00"), "7/26/22", "")
  

若需动态适配任意周期，可将周期起止时间放在指定单元格（如F1为7/26/22 08:00，F2为7/27/22 08:00），公式改为：

=IF(AND(B2<F2, C2>F1), TEXT(F1, "m/d/yy"), "")

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