sql-server - CTE 与额外的日期查找返回 NULL

Question

我正在尝试从货币假期表转换层次结构以选择 2022 年的特定日期。

源表:

<表类="s-表"><头>hol_ccy<日>假期 日期类型hol_dthol_day_nocalloc_idbase_hol_id<正文>瑞士法郎耶稣受难日日期2022-04-15 00:00:00.00009159空瑞士法郎耶稣受难日日期2012-04-06 00:00:00.00009169空瑞士法郎复活节星期一基于序数1899-12-30 00:00:00.000391889169瑞士法郎复活节基于序数1899-12-30 00:00:00.000291899169瑞士法郎升华基于序数1899-12-30 00:00:00.0003991909189瑞士法郎白色星期一基于序数1899-12-30 00:00:00.0005091919189

    CREATE TABLE MyTable (
      "hol_ccy" VARCHAR(3),
      "holiday" VARCHAR(13),
      "date_type" VARCHAR(13),
      "hol_dt" datetime,
      "hol_day_no" INTEGER,
      "calloc_id" INTEGER,
      "base_hol_id" INTEGER
    );
    
    INSERT INTO MyTable
      ("hol_ccy", "holiday", "date_type", "hol_dt", "hol_day_no", "calloc_id", "base_hol_id")
    VALUES
      ('CHF', 'Good Friday', 'Date', '2022-04-15 00:00:00.000', '0', '9159',null),
      ('CHF', 'Good Friday', 'Date', '2012-04-06 00:00:00.000', '0', '9169',null),
      ('CHF', 'Easter Monday', 'Ordinal Based', '1899-12-30 00:00:00.000', '3', '9188',9169),
      ('CHF', 'Easter', 'Ordinal Based', '1899-12-30 00:00:00.000', '2', '9189',9169),
      ('CHF', 'Ascension', 'Ordinal Based', '1899-12-30 00:00:00.000', '39', '9190',9189),
      ('CHF', 'Whit Monday', 'Ordinal Based', '1899-12-30 00:00:00.000', '50', '9191',9189);

令人讨厌的是，复活节星期一和复活节的基本假期与表中第一次出现的耶稣受难日有关(不幸的是我无法更改)我需要这个年 (2022) 发生耶稣受难日(工作)。但是，我不会返回 Ascension 和 Whit Monday 的预期计算值。

期望的输出:

<表类="s-表"><头>CCY<日>假期 <日>日期 <正文>瑞士法郎复活节星期一2022-04-18 00:00:00.000瑞士法郎复活节2022-04-17 00:00:00.000瑞士法郎升华2022-05-26 00:00:00.000瑞士法郎白色星期一2022-06-06 00:00:00.000

我的尝试:在这个社区的帮助下

    WITH cte
     AS (SELECT a.hol_ccy,
                a.holiday,
                a.hol_dt,
                a.hol_day_no,
                a.calloc_id,
                a.date_type
         FROM   MyTable a
         WHERE  a.base_hol_id IS NULL
         UNION ALL
         SELECT b.hol_ccy,
                b.holiday,
                Dateadd(day, b.hol_day_no, (SELECT c.hol_dt
                                            FROM   MyTable c
                                            WHERE  c.holiday = cte.holiday
                                                   AND c.hol_ccy = cte.hol_ccy
                                                   AND Year(c.hol_dt) = 2022)),
                b.hol_day_no,
                b.calloc_id,
                b.date_type
         FROM   MyTable b
                JOIN cte
                  ON cte.calloc_id = b.base_hol_id)
SELECT t.hol_ccy,
       t.holiday,
       t.hol_dt
FROM   cte t
WHERE  t.hol_ccy = 'CHF'
AND    t.date_type = 'Ordinal Based'

我的尝试输出:复活节和复活节星期一是正确的

<表类="s-表"><头>CCY<日>假期 <日>日期 <正文>瑞士法郎复活节星期一2022-04-18 00:00:00.000瑞士法郎复活节2022-04-17 00:00:00.000瑞士法郎升华空瑞士法郎白色星期一空

我是否需要嵌套额外的 CTE 来查找 2022 年耶稣升天和圣灵降临节的计算值？

Answer 1

这是来自 lptr的评论。我将其作为社区答案发布，因为他们没有根据要求将其作为答案发布。 lptr，如果您选择发布自己的答案，请将其标记为删除或发表评论。

for a single hierarchy of dates (not repeating per year)..

CREATE TABLE MyTable (
  "hol_ccy" VARCHAR(3),
  "holiday" VARCHAR(13),
  "date_type" VARCHAR(13),
  "hol_dt" datetime,
  "hol_day_no" INTEGER,
  "calloc_id" INTEGER,
  "base_hol_id" INTEGER
);

INSERT INTO MyTable
  ("hol_ccy", "holiday", "date_type", "hol_dt", "hol_day_no", "calloc_id", "base_hol_id")
VALUES
  ('CHF', 'Good Friday', 'Date', '2022-04-15 00:00:00.000', '0', '9159',null),
  ('CHF', 'Good Friday', 'Date', '2012-04-06 00:00:00.000', '0', '9169',null),
  ('CHF', 'Easter Monday', 'Ordinal Based', '1899-12-30 00:00:00.000', '3', '9188',9169),
  ('CHF', 'Easter', 'Ordinal Based', '1899-12-30 00:00:00.000', '2', '9189',9169),
  ('CHF', 'Ascension', 'Ordinal Based', '1899-12-30 00:00:00.000', '39', '9190',9189),
  ('CHF', 'Whit Monday', 'Ordinal Based', '1899-12-30 00:00:00.000', '50', '9191',9189);
GO
WITH cte
     AS (SELECT a.hol_ccy,
                a.holiday,
                a.hol_dt,
                a.hol_day_no,
                a.calloc_id,
                a.date_type,
                a.base_hol_id,
              --  a.hol_dt as thedate
                max(case when year(hol_dt)=2022 then hol_dt end) over(partition by hol_ccy,holiday) as thedate
         FROM   MyTable a
         WHERE  a.base_hol_id IS NULL
         UNION ALL
         SELECT b.hol_ccy,
                b.holiday,
                b.hol_dt,
                b.hol_day_no,
                b.calloc_id,
                b.date_type,
                b.base_hol_id,
                Dateadd(day, b.hol_day_no, cte.thedate) as thedate
                --case b.date_type when 'date' then b.hol_dt else Dateadd(day, b.hol_day_no, cte.thedate) end
         FROM   MyTable b
                JOIN cte
                  ON cte.calloc_id = b.base_hol_id)
SELECT t.hol_ccy,
       t.holiday,
       t.hol_dt, t.calloc_id, t.base_hol_id, t.date_type, t.thedate
FROM   cte t
WHERE  t.hol_ccy = 'CHF'
--AND    t.date_type = 'Ordinal Based'

db<>fiddle