如何修改SQL查询解决固定月频触发时月末日期匹配失效问题

问题根源 #

原查询中MONTHS维度的触发判断仅校验起始日与当日的日部分完全相等，若起始日为大月月末（如8月31日、1月31日），后续小月无对应日期时，会出现匹配失败的问题。

修改思路 #

针对MONTHS类型的触发规则，补充月末适配逻辑：

• 保留原有「间隔月份数为触发频率整数倍」的判断
• 日匹配规则调整为满足以下任一条件即可：
  • 起始日的日部分与当日的日部分完全相等
  • 起始日是其所属月份的最后一天，且当日是当前月份的最后一天

修改后的完整查询语句 #

SELECT
 *
FROM settings fs
WHERE CAST((
  CASE fs.trigger_metric
    WHEN 'MONTHS' THEN
        IIF(
          -- 间隔月份为触发频率的整数倍
          (DATEDIFF(month, fs.trigger_start_date, GETDATE()) % fs.trigger_frequency) = 0 
          AND (
            -- 情况1：日部分完全相等
            DATEPART(dd, fs.trigger_start_date) = DATEPART(dd, GETDATE())
            -- 情况2：适配月末场景，两端都是对应月份的最后一天即可匹配
            OR (EOMONTH(fs.trigger_start_date) = fs.trigger_start_date AND EOMONTH(GETDATE()) = CONVERT(DATE, GETDATE()))
          )
          , 1, 0)
    WHEN 'WEEKS' THEN
      IIF(DATEDIFF(week, fs.trigger_start_date, GETDATE()) % fs.trigger_frequency =0 
          AND DATEPART(dw, fs.trigger_start_date) = DATEPART(dw, GETDATE()), 1, 0)
    WHEN 'DAYS' THEN
      IIF((DATEDIFF(day, fs.trigger_start_date, GETDATE()) % fs.trigger_frequency) = 0, 1, 0)
    ELSE 0
    END
  ) AS BIT) = 1;

注：原语句中fs.trigger_start_date = Convert(DATE, GETDATE())的判断已经被后续逻辑覆盖，因此直接删除不影响执行结果

验证说明 #

以起始日为2023-08-31（8月最后一天）、触发规则为每月触发1次为例：

• 当日为2023-09-30（9月最后一天）时，间隔月份为1，模1等于0，且满足月末匹配规则，可正常命中
• 当日为2023-10-31（10月最后一天）时，日部分完全相等，可正常命中
  周、日维度的触发规则逻辑完全保留，不影响原有业务效果。

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