SQL Server中如何筛选日期范围：排除被后续新增条目覆盖的旧数据行

看起来你需要的是筛选掉那些被后续新增的日期范围条目完全覆盖的旧数据行，同时保留能覆盖整个目标区间的最新有效行，或者按天展示每天的最新成本。我来帮你梳理两种可行的方案：

首先先确认你的测试数据（方便我们复现场景）：

DROP TABLE IF EXISTS #ranges

CREATE TABLE #ranges (
    StartDate DATETIME2(7),
    EndDate DATETIME2(7),
    CreatedDate DATETIME2(7),
    Cost DECIMAL
);

INSERT INTO #ranges(StartDate, EndDate, CreatedDate, Cost)
VALUES
('2024-01-02 00:00:00', '2024-01-05 00:00:00', '2023-07-10 19:40:19.60', 10.00),
('2024-01-02 00:00:00', '2024-01-03 00:00:00', '2023-07-10 19:41:45.73', 5.00),
('2024-01-02 00:00:00', '2024-01-05 00:00:00', '2023-07-10 19:45:32.76', 22.00),
('2024-01-03 00:00:00', '2024-01-05 00:00:00', '2023-07-10 19:54:41.60', 44.00),
('2024-01-04 00:00:00', '2024-01-04 00:00:00', '2023-07-10 19:54:52.60', 50.00);

方案一：直接筛选未被后续条目覆盖的行 #

核心思路是：对于每一行，检查是否存在创建时间更晚的条目，能够完全覆盖当前行的日期范围。如果存在，就排除当前行；反之则保留。

对应的SQL查询：

SELECT *
FROM #ranges r
WHERE NOT EXISTS (
    SELECT 1
    FROM #ranges r2
    WHERE r2.CreatedDate > r.CreatedDate
      AND r2.StartDate <= r.StartDate
      AND r2.EndDate >= r.EndDate
);

执行这个查询后，会返回你期望的最后3行数据：

这是因为前两行的日期范围都被后续创建的条目完全覆盖了，所以会被过滤掉。

方案二：按天拆分，展示每日的最新成本 #

如果你的业务更关注每天的最新成本，而不是保留原始的日期范围行，那么可以先生成目标区间内的所有日期，再关联数据取每天的最新成本。

对应的SQL查询：

WITH DateRange AS (
    -- 生成目标日期区间内的所有日期
    SELECT MIN(StartDate) AS DateVal
    FROM #ranges
    UNION ALL
    SELECT DATEADD(DAY, 1, DateVal)
    FROM DateRange
    WHERE DateVal < (SELECT MAX(EndDate) FROM #ranges)
)
SELECT
    CONVERT(VARCHAR(10), dr.DateVal, 111) AS [Date], -- 转换为YYYY/MM/DD格式
    (
        -- 取当前日期对应的最新创建的成本
        SELECT TOP 1 Cost
        FROM #ranges r
        WHERE dr.DateVal BETWEEN r.StartDate AND r.EndDate
        ORDER BY r.CreatedDate DESC
    ) AS Cost
FROM DateRange dr
ORDER BY dr.DateVal;

执行后会得到你展示的按天结果：

这个方案更直观地展示了每一天的有效成本，适合需要按天统计的业务场景。

备注：内容来源于stack exchange，提问作者Andrew