SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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);
6 rows affected

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'


hol_ccy
holiday
hol_dt
calloc_id
base_hol_id
date_type
thedate


CHF
Good Friday
2022-04-15 00:00:00.000
9159
null
Date
2022-04-15 00:00:00.000

CHF
Good Friday
2012-04-06 00:00:00.000
9169
null
Date
2022-04-15 00:00:00.000

CHF
Easter Monday
1899-12-30 00:00:00.000
9188
9169
Ordinal Based
2022-04-18 00:00:00.000

CHF
Easter
1899-12-30 00:00:00.000
9189
9169
Ordinal Based
2022-04-17 00:00:00.000

CHF
Ascension
1899-12-30 00:00:00.000
9190
9189
Ordinal Based
2022-05-26 00:00:00.000

CHF
Whit Monday
1899-12-30 00:00:00.000
9191
9189
Ordinal Based
2022-06-06 00:00:00.000


Warning: Null value is eliminated by an aggregate or other SET operation.


select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) 
 Jul 25 2020 11:26:55 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

hol_ccy	holiday	hol_dt	calloc_id	base_hol_id	date_type	thedate
CHF	Good Friday	2022-04-15 00:00:00.000	9159	null	Date	2022-04-15 00:00:00.000
CHF	Good Friday	2012-04-06 00:00:00.000	9169	null	Date	2022-04-15 00:00:00.000
CHF	Easter Monday	1899-12-30 00:00:00.000	9188	9169	Ordinal Based	2022-04-18 00:00:00.000
CHF	Easter	1899-12-30 00:00:00.000	9189	9169	Ordinal Based	2022-04-17 00:00:00.000
CHF	Ascension	1899-12-30 00:00:00.000	9190	9189	Ordinal Based	2022-05-26 00:00:00.000
CHF	Whit Monday	1899-12-30 00:00:00.000	9191	9189	Ordinal Based	2022-06-06 00:00:00.000