CREATE TABLE DIM_DATE
(date_key date,
work_day_flag varchar,
bank_holiday varchar);
insert into DIM_DATE
values
('2024-03-27','Y','N'),
('2024-03-28','Y','N'),
('2024-03-29','Y','Y'),
('2024-03-30','N','N'),
('2024-03-31','N','N'),
('2024-04-01','Y','Y'),
('2024-04-02','Y','N'),
('2024-04-03','Y','N'),
('2024-04-04','Y','N'),
('2024-04-05','Y','N'),
('2024-04-06','N','N'),
('2024-04-07','N','N'),
('2024-04-08','Y','N'),
('2024-04-09','Y','N'),
('2024-04-10','Y','N'),
('2024-04-11','Y','N'),
('2024-04-12','Y','N'),
('2024-04-13','N','N');
WITH
DAYS3 AS (
SELECT
DATE_KEY,
Lead(DATE_KEY, 3) OVER (ORDER BY DATE_KEY) AS work_days_3_incl_bh
FROM
DIM_DATE
WHERE
WORK_DAY_FLAG = 'Y'
AND BANK_HOLIDAY = 'N'
)
SELECT
A.DATE_KEY,
(
SELECT TOP(1) work_days_3_incl_bh
FROM DAYS3
WHERE
DAYS3.DATE_KEY >= A.DATE_KEY AND
work_days_3_incl_bh > A.DATE_KEY ORDER BY work_days_3_incl_bh
) work_days_3_incl_bh
FROM DIM_DATE A
ORDER BY 1;