Nested Hierarchy to Calculated Date-CodePudding

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

Sample source table for explanation:

 --------- --------------- --------------- ------------------------- ------------ ----------- ------------- 
| 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          | 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        |
 --------- --------------- --------------- ------------------------- ------------ ----------- -------------

Desired Output:

CCY    HOLIDAY          DATE

CHF    Good Friday      2022-04-15 00:00:00.000
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

Row 1 is a given fact entered into the database for each year. Given as date_type: date
Rows 2 & 3 are based on Row 1. Each adding the value of hol_day_no to row 1's hol_dt (date). This relationship is described in calloc_id and base_hol_id columns
Rows 4 & 5 are based on Row 3.

I cant figure out how to treat the nesting of the ordinal based date types in SQL. Any pointers would be appreciated.

CodePudding user response：

It looks like a pretty simple recursive CTE is needed. You just need to add the child row's day number to the parent row's date.

WITH cte AS (
    SELECT
      t.hol_ccy,
      t.holiday,
      t.hol_dt,
      t.calloc_id
    FROM YourTable t
    WHERE t.base_hol_id IS NULL
    
    UNION ALL
    
    SELECT
      t.hol_ccy,
      t.holiday,
      DATEADD(day, t.hol_day_no, cte.hol_dt),
      t.calloc_id
    FROM YourTable t
    JOIN cte ON cte.calloc_id = t.base_hol_id
)
SELECT
  t.hol_ccy,
  t.holiday,
  t.hol_dt
FROM cte t;

db<>fiddle