如何用DateDiff实现视图动态日期过滤：排除历史数据处理新数据

首先拆解你的需求核心：

• 部署后0-99天：仅返回部署当日及之后插入且LastModifiedDate距今≤80天的记录，完全忽略部署前已存在的、即使满足≤80天条件的历史数据；部署首日无新数据时视图返回空。
• 部署100天及以后：恢复原始逻辑，仅返回所有LastModifiedDate距今≤80天的记录。

推荐方案：使用配置表存储部署日期（稳定可靠） #

这种方式的好处是部署日期不会因为后续修改视图而改变，适合长期维护。

步骤1：创建部署配置表（如果尚未存在） #

先建一个专门存储关键配置的表，用来记录视图的上线日期：

CREATE TABLE dbo.DeploymentSettings (
    SettingKey VARCHAR(50) PRIMARY KEY,
    SettingValue DATETIME NOT NULL
);

-- 插入视图的实际部署日期，替换为你上线当天的日期
INSERT INTO dbo.DeploymentSettings (SettingKey, SettingValue)
VALUES ('vAeoiSurplusCaseCreation_DeployDate', '2024-05-20');

步骤2：修改视图逻辑 #

更新视图的SQL，加入分阶段的过滤条件：

ALTER VIEW [dbo].[vAeoiSurplusCaseCreation] 
AS 
DECLARE @DeployDate DATETIME;
DECLARE @DaysSinceDeploy INT;

-- 从配置表中获取部署日期
SELECT @DeployDate = SettingValue
FROM dbo.DeploymentSettings
WHERE SettingKey = 'vAeoiSurplusCaseCreation_DeployDate';

-- 计算部署至今的天数
SET @DaysSinceDeploy = DATEDIFF(dd, @DeployDate, GETDATE());

SELECT AccessNumber, DocumentID, LastModifiedDate, StatusCode 
FROM AeoiCaptureLog 
WHERE StatusCode IN (6, 13, 15)
AND (
    -- 阶段2：部署满100天，恢复原始的最近80天过滤逻辑
    (@DaysSinceDeploy >= 100 AND DATEDIFF(dd, LastModifiedDate, GETDATE()) <= 80)
    OR
    -- 阶段1：部署未满100天，仅保留部署后插入且符合80天条件的记录
    (@DaysSinceDeploy < 100 
     AND LastModifiedDate >= @DeployDate 
     AND DATEDIFF(dd, LastModifiedDate, GETDATE()) <= 80)
);

备选方案：利用视图的创建日期（无需额外表） #

如果不想新增配置表，可以用视图的创建日期作为部署日期，但要注意：后续如果修改（ALTER）视图，创建日期会被更新，所以仅适合视图不会被频繁修改的场景：

ALTER VIEW [dbo].[vAeoiSurplusCaseCreation] 
AS 
DECLARE @DeployDate DATETIME;
DECLARE @DaysSinceDeploy INT;

-- 获取视图的创建日期作为部署日期
SELECT @DeployDate = create_date
FROM sys.views
WHERE name = 'vAeoiSurplusCaseCreation' AND schema_id = SCHEMA_ID('dbo');

SET @DaysSinceDeploy = DATEDIFF(dd, @DeployDate, GETDATE());

SELECT AccessNumber, DocumentID, LastModifiedDate, StatusCode 
FROM AeoiCaptureLog 
WHERE StatusCode IN (6, 13, 15)
AND (
    (@DaysSinceDeploy >= 100 AND DATEDIFF(dd, LastModifiedDate, GETDATE()) <= 80)
    OR
    (@DaysSinceDeploy < 100 
     AND LastModifiedDate >= @DeployDate 
     AND DATEDIFF(dd, LastModifiedDate, GETDATE()) <= 80)
);

关键逻辑说明 #

• 部署首日：@DaysSinceDeploy为0，此时只会筛选LastModifiedDate >= @DeployDate的记录，如果没有新插入的数据，视图自然返回空，符合要求。
• 部署后0-99天：完全排除部署前的历史数据，即使那些数据的LastModifiedDate也在80天范围内。
• 部署100天后：自动切换回原始逻辑，不用手动修改视图。

测试建议 #

你可以通过调整@DeployDate的值来模拟不同阶段：

• 测试阶段1：把@DeployDate设为GETDATE()-90，验证是否只返回最近90天内插入的且≤80天的记录。
• 测试阶段2：把@DeployDate设为GETDATE()-100，验证是否返回所有最近80天的记录。

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