CTE with additional date lookup returning NULL

user_12439754 0
Viewed 85

I am trying to convert a hierarchy from a currency holiday table to select the specific date occurrence for 2022.

Source table:

hol_ccy holiday date_type hol_dt hol_day_no calloc_id base_hol_id
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 
    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);

Annoyingly the base holiday for Easter Monday and Easter are tied to the first occurrence of Good Friday in the table (I can't change this unfortunately) I need this years (2022) occurrence of Good Friday (Working). I am however not returning the expected calculated values for Ascension and Whit Monday.

Desired Output:

CCY HOLIDAY DATE
CHF Easter Monday 2022-04-18 00:00:00.000
CHF Easter 2022-04-17 00:00:00.000
CHF Ascension 2022-05-26 00:00:00.000
CHF Whit Monday 2022-06-06 00:00:00.000

My Attempt: with the help from this community 

    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'

The Output of my Attempt: Easter and Easter Monday are correct

CCY HOLIDAY DATE
CHF Easter Monday 2022-04-18 00:00:00.000
CHF Easter 2022-04-17 00:00:00.000
CHF Ascension NULL
CHF Whit Monday NULL

Do I need to nest an additional CTE to look for the 2022 Ascension and Whit Monday calculated values?

sql-server
1 Answers

This is from lptr's comment. I am posting it as a community answer as they did not post it as an answer upon request. lptr, if you chose to post your own answer, please flag this for removal or comment.

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

user_20299830
Related