Matching holidays between two dates (SAS TO SQL)

Question

I have two tables one with request dates and SLA and the other with the list of dates of US holidays.

I want to write query that checks if any of the holidays fall between the request dates and the SLA dates and it falls on any day Monday through Friday. If yes then a column with value 1 should be populated else 0.

I have the task coded in SAS but as we migrate away from SAS, we need to get it done using SQL.

Attaching a screenshot of the SAS code

it would be easier to help you if you provided some sample data and desired output. By the way, it is better to provide code as text, not as image — Sergey
– Sergey, Commented Jun 5 at 19:05
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K
– Dale K, Commented Jun 5 at 20:13
Looks like a variation of the "working day number" (increasing if it's not a weekend/holiday) logic. Check stackoverflow.com/a/43040273/2527905 — dnoeth
– dnoeth, Commented Jun 6 at 15:25
Yes, I agree on @dnoeth 's proposal (which is what the comment in my answer "An alternative would be to use window functions to mark each day […]" artisanally expressed, with an additional hint about limiting the depth of the calendar CTE to only 2 * SLA + 7 days) — Guillaume Outters
– Guillaume Outters, Commented Jun 10 at 4:48

Guillaume Outters · Accepted Answer · 2025-06-05 20:41:56Z

2

Pseudo-code

As some comments still wait for answers, and as I have no Teradata to test on, here is some pseudo-SQL that focuses on the interesting part of determining until when the SLA will run over week ends and holidays:

-- Use recursion to walk through all days until our SLA reaches 0 after having been decremented on each working day.
-- An alternative would be to use window functions to mark each day of the theorical SLA period as working or non working (which would expand the actual SLA). /!\ This would require that we explore more than the initial SLA, for example 2 * SLA + 7 days, so that if the theorical SLA ended on a Satursday, we have also seen the Sunday (week end, so expands SLA), and possibly the holy Monday (expands SLA), until reaching the next working Tuesday.
with recursive
    r as
    (
        select d, sla, d cur, sla remain from req
        union all
        select
            d, sla,
            cur + 1,
            remain -
            case
                when extract(dow from cur + 1) in (0, 6) then 0
                when exists(select 1 from holiday h where h.d = cur + 1) then 0
                else 1
            end
        from r
        where remain > 0
    )
select * from r where remain = 0;

d	sla	cur	remain	comment
2025-05-23	4	2025-05-30	0	23 was a friday, 24 and 25 on week end, 26 is a holiday; so SLA runs on 23, 27, 28 and 29: due date is the 30th
2024-12-20	15	2025-01-14	0	Christmas + New Year + 4 week ends + 15 days SLA = 25 days later

… Well OK in fact this pseudo-SQL works on PostgreSQL.

answered Jun 5 at 20:41

Guillaume Outters

7,5121 gold badge22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fred Jun 6 at 14:33

Teradata could use DayOfWeek function (which returns 1-7) instead of extract(dow...). Also Teradata won't accept EXISTS subquery in recursive context - but you can LEFT JOIN to the holiday table and check h.d IS NOT NULL in the CASE as equivalent.

Guillaume Outters Jun 6 at 15:03

Thanks @Fred for the porting hints! I was aware of the DayOfWeek in (1, 7) diff, but I didn't know about the exists limitation. I was waiting that OP precises his need (full-fledged solution or really only flag individual days?) before making the effort to translate to Teradata (even if I used as generic SQL as possible, after having quickly verified that Teradata had recursive CTEs). Feel free to post your own answer (based on mine or not).

Collectives™ on Stack Overflow

Matching holidays between two dates (SAS TO SQL)

1 Answer 1

Pseudo-code

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Pseudo-code

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related