基于Brand获取下一个变更日期的SQL查询需求求助

原查询使用LEAD()按IdBusinessUnit,Brand分区，未处理ALL品牌的特殊规则——ALL对应的RetailDate需作为其他品牌的优先变更日期，导致结果不符合预期。以下是修正后的解决方案：

核心思路 #

1. 先提取每个业务单元下ALL品牌的有效日期，这些日期是全局变更节点，优先级高于同品牌的后续日期
2. 对每个非ALL品牌行，同时计算两个候选变更日期：同品牌的下一个日期、当前日期之后最早的ALL日期，取两者中较小的作为NextChange
3. 无候选日期时返回GETDATE()，ALL品牌行直接返回GETDATE()

修正后的SQL语句 #

WITH AllDates AS (
    -- 提取各业务单元下的ALL品牌有效日期
    SELECT 
        IdBusinessUnit,
        RetailDate AS AllRetailDate
    FROM tbl_Params
    WHERE Brand = 'ALL' AND bStdActive = 1
),
BrandNextDates AS (
    -- 计算每个品牌自身的下一个变更日期
    SELECT 
        RetailDate,
        Brand,
        IdBusinessUnit,
        LEAD(RetailDate) OVER(PARTITION BY IdBusinessUnit, Brand ORDER BY RetailDate) AS BrandNextDate
    FROM tbl_Params P (NOLOCK)
    WHERE bStdActive = 1
)
SELECT 
    b.RetailDate,
    b.Brand,
    -- 按规则生成NextChange列
    CASE
        WHEN b.Brand = 'ALL' THEN GETDATE()
        ELSE COALESCE(
            -- 优先取当前日期之后最早的ALL日期
            (SELECT MIN(ad.AllRetailDate) 
             FROM AllDates ad 
             WHERE ad.IdBusinessUnit = b.IdBusinessUnit 
               AND ad.AllRetailDate > b.RetailDate),
            -- 若无ALL日期则取品牌自身下一个日期
            b.BrandNextDate,
            -- 都无则返回当前日期
            GETDATE()
        )
    END AS NextChange,
    b.IdBusinessUnit
FROM BrandNextDates b
ORDER BY b.RetailDate;

逻辑验证 #

• 对于B Series的2023-02-07行，后续最早的ALL日期是2023-02-21，早于自身下一个日期2023-03-10，因此NextChange取2023-02-21，符合预期
• 对于Equity的2023-02-15行，自身无后续日期，取后续最早的ALL日期2023-02-21，符合预期
• ALL行及无后续变更的行返回GETDATE()，符合要求

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