如何编写SQL语句筛选符合特定timesheet提交周期要求的申请人名单

需求拆解 #

• 条件1：申请人存在上周的timesheet提交记录
• 条件2：申请人在上周往前推6周的区间内无任何提交记录
• 条件3：申请人在上述6周之前的时间段存在过提交记录

日期边界说明 #

先统一按周维度划分三个计算区间，你可以根据实际业务的周起始规则（比如周一/周日为周首）调整参数：

1. 上周提交区间：最近7天的提交记录
2. 空窗6周区间：上周往前数第8天到第49天的时间段（合计6周）
3. 历史提交区间：早于49天前的所有时间段

完整SQL代码 #

SELECT
    t.[TimesheetID],
    t.[PeriodStarting],
    t.[Sector],
    t.[ApplicantId]
FROM
    [WebServices].[TimesheetEntity] t
-- 主查询先过滤上周提交，满足条件1的基础数据范围
WHERE
    t.[PeriodStarting] > DATEADD(DAY, -7, GETDATE())
    -- 条件2：校验同一申请人中间6周无提交
    AND NOT EXISTS (
        SELECT 1 
        FROM [WebServices].[TimesheetEntity] t2
        WHERE t2.ApplicantId = t.ApplicantId
        AND t2.PeriodStarting BETWEEN DATEADD(DAY, -49, GETDATE()) AND DATEADD(DAY, -8, GETDATE())
    )
    -- 条件3：校验同一申请人6周前有历史提交
    AND EXISTS (
        SELECT 1 
        FROM [WebServices].[TimesheetEntity] t3
        WHERE t3.ApplicantId = t.ApplicantId
        AND t3.PeriodStarting <= DATEADD(DAY, -49, GETDATE())
    )

补充说明 #

如果需要严格对齐自然周避免天数跨周误差，可以用周差计算替代天数差：上周条件写为DATEDIFF(week, [PeriodStarting], GETDATE()) = 1，中间6周条件写为DATEDIFF(week, [PeriodStarting], GETDATE()) BETWEEN 2 AND 7，历史提交条件写为DATEDIFF(week, [PeriodStarting], GETDATE()) > 7即可。
如果你只需要输出符合条件的申请人唯一清单，不需要明细记录，可以把主查询的SELECT部分改为SELECT DISTINCT t.ApplicantId即可。

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