如何在SQL查询中按工作日设置数据保留过滤（排除周末及节假日）

核心思路 #

要实现仅保留最近N个工作日的数据，关键是准确计算出N个工作日之前的截止日期，而非按自然日推算。利用已有的dbo.fn_previous_working_day函数，循环N次每次往前跳一个工作日，就能得到正确的截止日期。

修正后的脚本实现 #

第一步：计算目标截止日期 #

先修正循环逻辑，确保每次都基于上一个工作日继续往前推：

DECLARE @DataRetentionPeriod INT = 5; -- 要保留的工作日天数
DECLARE @CurrentDate DATETIME = GETDATE(); -- 基准日期，可替换为指定日期如'2024-04-04'
DECLARE @CutoffDate DATETIME = @CurrentDate;

-- 循环N次，每次获取上一个工作日
WHILE @DataRetentionPeriod > 0
BEGIN
    SET @CutoffDate = dbo.fn_previous_working_day(@CutoffDate);
    SET @DataRetentionPeriod = @DataRetentionPeriod - 1;
END

-- 此时@CutoffDate就是N个工作日之前的日期

第二步：整合到删除语句 #

用计算出的@CutoffDate作为过滤条件，删除早于该日期的数据：

DECLARE @DataRetentionPeriod INT = 5;
DECLARE @CurrentDate DATETIME = GETDATE();
DECLARE @CutoffDate DATETIME = @CurrentDate;

WHILE @DataRetentionPeriod > 0
BEGIN
    SET @CutoffDate = dbo.fn_previous_working_day(@CutoffDate);
    SET @DataRetentionPeriod = @DataRetentionPeriod - 1;
END

-- 删除截止日期之前的数据
DELETE FROM dbo.table1 pt
WHERE CONVERT(VARCHAR, pt.ptStampDateTime, 112) < CONVERT(VARCHAR, @CutoffDate, 112);

逻辑验证（以2024-04-04为例） #

假设基准日期是2024-04-04（周四），要保留5个工作日：

1. 第1次循环：fn_previous_working_day('2024-04-04') → 2024-04-03（周三）
2. 第2次：→ 2024-04-02（周二）
3. 第3次：→ 2024-04-01（周一）
4. 第4次：→ 2024-03-29（周五，因为30、31是周末）
5. 第5次：→ 2024-03-28（周四）
   最终得到的截止日期是2024-03-28，删除该日期之前的数据，即可保留最近5个工作日（4.1、4.2、4.3、4.4、3.29）的数据，符合预期。

可选优化：封装成自定义函数 #

如果需要多次使用，可将计算截止日期的逻辑封装成函数，方便调用：

CREATE FUNCTION dbo.fn_get_cutoff_working_day(@BaseDate DATETIME, @RetentionDays INT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @CutoffDate DATETIME = @BaseDate;
    WHILE @RetentionDays > 0
    BEGIN
        SET @CutoffDate = dbo.fn_previous_working_day(@CutoffDate);
        SET @RetentionDays = @RetentionDays - 1;
    END
    RETURN @CutoffDate;
END

调用示例：

DECLARE @CutoffDate DATETIME = dbo.fn_get_cutoff_working_day(GETDATE(), 5);

DELETE FROM dbo.table1 pt
WHERE CONVERT(VARCHAR, pt.ptStampDateTime, 112) < CONVERT(VARCHAR, @CutoffDate, 112);

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