SQL - Calculating Date Offset While Excluding Weekends

In a current project, I needed to calculate the date offset while excluding weekends. Initially, this was solved using a loop in PL/SQL, which I consider a less efficient approach.

To optimize it, I created a straightforward SQL query that handles this logic directly, ensuring only business days are considered.

I also enhanced the query to include the ability to move both backward and forward in time, all controlled by a single bind variable that defines the offset.

SELECT
    d
FROM
    (
        SELECT
            d,
            ROWNUM rn
        FROM
            (
                SELECT
                    sysdate + ( ( level ) * abs(:p_offset) / :p_offset )         d,
                    to_char(sysdate +(level), 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') AS target_day
                FROM
                    dual
                CONNECT BY
                    level <= abs(:p_offset) * 2
            )
        WHERE
            target_day NOT IN ( 'SAT', 'SUN' )
    )
WHERE
    rn = abs(:p_offset);

In case you want to implement holidays as well, you can rely on a REST API for that:

WITH dy AS (
    SELECT
        sysdate + ( ( level ) * abs(:p_offset) / :p_offset )         d,
        to_char(sysdate +(level), 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') AS target_day
    FROM
        dual
    CONNECT BY
        level <= abs(:p_offset) * 2
), dd AS (
    SELECT
        MIN(d) mind,
        MAX(d) maxd
    FROM
        dy
), h AS (
    SELECT
        TO_DATE(holiday_date, 'yyyy-mm-dd') holiday_date,
        holiday_type
    FROM
        JSON_TABLE ( apex_web_service.make_rest_request(p_url => 'https://date.nager.at/api/v3/PublicHolidays/2024/US', p_http_method => 'GET'
        ), '$[*]'
            COLUMNS (
                holiday_date VARCHAR2 ( 20 ) PATH '$.date',
                holiday_type VARCHAR2 ( 50 ) PATH '$.types[0]'
            )
        )
    WHERE
        holiday_type = 'Public'
), hh AS (
    SELECT
        holiday_date
    FROM
             h
        CROSS JOIN dd
    WHERE
        holiday_date BETWEEN mind AND maxd
)
SELECT
    *
FROM
    (
        SELECT
            d,
            ROWNUM rn
        FROM
            (
                SELECT
                    trunc(d) d
                FROM
                    dy
                WHERE
                    target_day NOT IN ( 'SAT', 'SUN' )
                MINUS
                SELECT
                    holiday_date d
                FROM
                    hh
            )
    )
WHERE
    rn = abs(:p_offset);

Enjoy Life!