sqlsql-serversql-server-2016

SQL Query to increase a DATE by X number of working days including Bank Holidays

I'm looking for ideas on increasing a DATE by 3 working days, including Bank Holidays. It has to be done in a query, as we don't have privileges to create functions or procs.

We have a Dim Date table with Working day and Bank Holiday Flags.

DECLARE @DIM_DATE TABLE
(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');

My current code is flawed as it holds NULL values for the non working days and bank holidays.

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,
    DAYS3.work_days_3_incl_bh
FROM @DIM_DATE A
LEFT JOIN DAYS3 ON A.DATE_KEY = DAYS3.DATE_KEY
ORDER BY 1;

This is the output I get:-

enter image description here

This is the output I'm trying to achieve:-

enter image description here

I really appreciate any help you can provide.

Solution

  • The possible solution - using sub-query:

        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;

    Test it here: https://sqlize.online/s/75