SQL Server 2017中计算重叠日期区间天数并拆分首7天与剩余天数

核心思路 #

先计算每条记录与指定区间的实际重叠区间，再基于该区间拆分首7天和剩余天数，避免嵌套复杂的CASE WHEN，逻辑更清晰易维护。

完整SQL代码 #

DECLARE @StartDate DATE = '2022-01-01';
DECLARE @EndDate DATE = '2022-01-31';
DECLARE @First7End DATE = DATEADD(DAY, 6, @StartDate); -- 首7天结束日（含起始日共7天）

SELECT
    ID,
    -- 计算首7天内的重叠天数
    CASE
        WHEN overlap_end < @StartDate THEN 0
        WHEN overlap_start > @First7End THEN 0
        ELSE DATEDIFF(DAY, overlap_start, MIN(overlap_end, @First7End)) + 1
    END AS [First 7 days],
    -- 剩余天数 = 总重叠天数 - 首7天天数（确保非负）
    MAX(0, total_overlap_days - 
        CASE
            WHEN overlap_end < @StartDate THEN 0
            WHEN overlap_start > @First7End THEN 0
            ELSE DATEDIFF(DAY, overlap_start, MIN(overlap_end, @First7End)) + 1
        END
    ) AS [Remaining days]
FROM (
    -- 子查询预计算每条记录的实际重叠区间及总天数
    SELECT
        ID,
        MAX([From date], @StartDate) AS overlap_start,
        MIN([To date], @EndDate) AS overlap_end,
        DATEDIFF(DAY, MAX([From date], @StartDate), MIN([To date], @EndDate)) + 1 AS total_overlap_days
    FROM YourTable
    -- 修正重叠筛选条件（覆盖所有与指定区间有交集的记录）
    WHERE [From date] <= @EndDate AND [To date] >= @StartDate
) AS OverlapCalculations;

关键说明 #

1. 修正筛选条件：原WHERE子句仅筛选完全包含指定区间的记录，替换为[From date] <= @EndDate AND [To date] >= @StartDate可覆盖所有重叠场景（比如记录区间是2021-12-25到2022-01-05这类部分重叠的情况）。
2. 重叠区间计算：用MAX和MIN快速定位记录与指定区间的实际重叠起止日，避免多次嵌套判断。
3. 首7天拆分：通过@First7End固定首7天的结束点，再对比重叠区间计算有效天数，逻辑直观。
4. 剩余天数计算：直接用总重叠天数减去首7天天数，并用MAX(0, ...)确保结果非负（避免首7天覆盖全部重叠区间时出现负数）。

简化版（避免重复计算首7天天数） #

如果想进一步精简代码，可在子查询中先计算首7天天数：

DECLARE @StartDate DATE = '2022-01-01';
DECLARE @EndDate DATE = '2022-01-31';
DECLARE @First7End DATE = DATEADD(DAY, 6, @StartDate);

SELECT
    ID,
    first_7_days AS [First 7 days],
    MAX(0, total_overlap_days - first_7_days) AS [Remaining days]
FROM (
    SELECT
        ID,
        -- 计算首7天重叠天数，无重叠则为0
        DATEDIFF(DAY, MAX([From date], @StartDate), MIN(MIN([To date], @EndDate), @First7End)) + 1
            * CASE WHEN MAX([From date], @StartDate) <= MIN(MIN([To date], @EndDate), @First7End) THEN 1 ELSE 0 END
            AS first_7_days,
        DATEDIFF(DAY, MAX([From date], @StartDate), MIN([To date], @EndDate)) + 1 AS total_overlap_days
    FROM YourTable
    WHERE [From date] <= @EndDate AND [To date] >= @StartDate
) AS Calculations;

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