如何为日期维度表创建基于可变狂欢节的狂欢季标记列

你之前的问题核心是子查询没有关联当前日期的年份，导致要么返回所有年份的狂欢节日期触发多行报错，要么TOP 1取到最早的日期无法匹配当前行所属年份。以下是两种可行的解决思路：

方法1：关联子查询（直接在CASE中匹配年份） #

直接基于当前日期的年份，在子查询中筛选对应年份的狂欢节日期，同时用原生日期类型比较（避免字符串格式化带来的逻辑错误）：

MardiGrasFlag = 
CASE
    -- 判断当前日期在当年1月6日之后，且不晚于当年狂欢节
    WHEN [Date] >= DATEFROMPARTS(YEAR([Date]), 1, 6)
         AND [Date] <= (
             SELECT h.[Date]
             FROM dbo.HolidayDimension AS h
             WHERE YEAR(h.[Date]) = YEAR([Date]) -- 关键：关联当前日期的年份
               AND h.[Holiday Text] = 'Mardi Gras'
         ) THEN 'YES'
    ELSE 'NO' -- 可根据需求调整默认值，比如留空
END

方法2：预生成年度狂欢节表再关联（适合大数据量场景） #

先提取出每年的狂欢节日期，再通过年份关联到日期维度表，查询效率更优：

WITH YearlyMardiGras AS (
    SELECT 
        YEAR([Date]) AS HolidayYear,
        [Date] AS MardiGrasDate
    FROM dbo.HolidayDimension
    WHERE [Holiday Text] = 'Mardi Gras'
)
SELECT 
    d.*,
    CASE
        WHEN d.[Date] >= DATEFROMPARTS(YEAR(d.[Date]), 1, 6)
             AND d.[Date] <= y.MardiGrasDate THEN 'YES'
        ELSE 'NO'
    END AS MardiGrasFlag
FROM dbo.DateDimension d
LEFT JOIN YearlyMardiGras y ON YEAR(d.[Date]) = y.HolidayYear;

原方案问题解析 #

• 未关联年份的子查询会返回所有历史狂欢节日期，导致CASE语句无法处理多行结果，触发报错；
• 添加TOP 1后，默认按表中存储顺序取最早的狂欢节日期，无法匹配当前行的年份，导致标记逻辑错误。

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