Business Day Calculation with a Non-Business Day Table (Oracle)

Why Business Day Calculation Is Trickier Than It Looks

"If an order hasn't shipped within 4 business days, send a reminder email."

Sounds simple. But once you start implementing, the edge cases pile up fast. You need to exclude weekends, statutory holidays, substitute holidays, and — the real headache — ad-hoc public holidays that get declared with little notice. Calling an external API on every check is wasteful. Hardcoding holiday lists means you can't react when the government suddenly declares next Monday a day off.

This post covers how to design a system that syncs holidays from a government open data API, stores them alongside weekends in a single table, and calculates business days in one SQL query.

Prerequisites

• Oracle Database (uses CONNECT BY, MERGE)
• Java / Spring / MyBatis
• Spring Batch for scheduling
• Access to a government holiday API (this post uses Korea's open data portal, but the pattern applies to any country's equivalent)

The First Design Decision: Holiday Table vs Non-Business Day Table

The intuitive approach is a "holiday table" — store New Year's Day, Christmas, national holidays, and look them up when calculating business days.

The problem: this splits your business day logic across two layers. "Is it a weekend?" gets answered in application code. "Is it a holiday?" gets answered by the database. When you need to join against an orders table to batch-identify overdue shipments, your query ends up with a TO_CHAR(dt, 'DY') weekday check plus a holiday subquery. It works, but it's messy and error-prone.

A non-business day table solves this at the data level. Weekends, statutory holidays, ad-hoc holidays, and company-specific days off all go into the same table. Business day calculation reduces to NOT IN (non_business_day_table).

The key columns:

• BASE_DT VARCHAR2(8) — the date (YYYYMMDD), with a UNIQUE constraint
• DAY_TP_CD VARCHAR2(10) — type code: weekend, public holiday, ad-hoc holiday, company holiday
• DATA_SRC_CD VARCHAR2(10) — source: system-generated (weekends), API, or manual entry

Separating type and source matters because they answer different questions. Type tells you why it's a day off. Source tells you how it got into the system — which you need for auditing and deduplication.

Syncing Holidays from a Government API

Most countries offer some form of public holiday data through open data portals. The pattern described here uses Korea's API, but the architecture applies universally: call the API with a year parameter, get back a list of holidays, filter for actual days off, and insert missing ones.

One important detail: filter by the "is this a day off" flag, not just by the holiday category. Some memorial days are officially recognized but don't result in a day off for public institutions.

The real challenge isn't the API call itself — it's what happens with ad-hoc holidays. These are declared by government decree, sometimes with only days of notice. The API provider updates their data manually after the official announcement, but there's no guaranteed timeline. It could be same-day or several days later.

Batch Strategy: Semi-Annual + Monthly + Manual

How often to sync is the practical design question.

Semi-annual batch (January and July): Generate weekends for the current year and next year using MERGE. Call the API and insert holidays. Skip dates that already exist. Why not annual? If you only sync in January, late-December orders that need business day + 4 into January will have no data for the new year.

Monthly batch (1st of each month): Re-call the API for the current year to catch any newly declared holidays. This is the primary mechanism for automatically picking up ad-hoc holidays — assuming the API has been updated by then.

Manual fallback: Two admin capabilities. First, an API re-trigger button that calls the API immediately without waiting for the monthly batch. Second, a direct entry form where an admin can type in a date and description for a holiday that hasn't hit the API yet, or for company-specific days off.

Weekend generation uses Oracle's MERGE INTO. CONNECT BY generates every date in the year, a WHERE clause filters for Saturdays and Sundays, and MERGE skips existing rows. This makes the operation idempotent — run it as many times as you want with no duplicates.

The Business Day + N Query

With the non-business day table populated, the calculation works in three steps.

Step 1: Generate candidate dates after the base date using CONNECT BY LEVEL. Step 2: Exclude non-business days with NOT IN. Step 3: Pick the Nth business day with ROW_NUMBER().

SELECT dt FROM (
    SELECT dt, ROW_NUMBER() OVER (ORDER BY dt) AS biz_seq
      FROM (
          SELECT TO_CHAR(TO_DATE(:baseDt, 'YYYYMMDD') + LEVEL, 'YYYYMMDD') AS dt
            FROM DUAL
         CONNECT BY LEVEL <= (:addDays * 3)
      )
     WHERE dt NOT IN (
         SELECT BASE_DT FROM TB_NON_BSNS_DAY WHERE USE_YN = 'Y'
     )
) WHERE biz_seq = :addDays;

The buffer is addDays * 3. Why? In the worst case, extended national holidays plus surrounding weekends plus substitute holidays can create 9–10 consecutive non-business days. For a 4-business-day calculation, a buffer of 12 candidate dates is more than enough.

If the buffer runs out (no Nth business day found among candidates), the query returns NULL. Your service layer must handle this.

You can join this pattern directly against an orders table to batch-extract overdue shipments in a single query — no application-level loops needed.

Ad-Hoc Holidays: The Problem You Can't Fully Automate

The hardest part of this system isn't technical. It's operational.

Ad-hoc holidays get declared with short notice, and the API update has no SLA. No matter how tight your batch cycle is, there's a window where your calculation could be wrong. If the purpose of the calculation is triggering reminder emails, getting it wrong means sending "your order is late" notifications when the deadline hasn't actually passed — a direct hit to customer experience.

The real solution is a process, not more code: when the government announces an ad-hoc holiday, someone on the team registers it manually through the admin interface. The monthly batch and API trigger button are technical backstops, but the operational workflow is what actually covers the gap.

Key Takeaways

Store all non-business days in one table, not just holidays. Let the data absorb the complexity so every consumer — batches, APIs, reports — uses the same source of truth. Use MERGE and existence checks for idempotent syncs that are safe to re-run. And accept that ad-hoc holidays are an operational problem that needs an operational solution alongside the technical one.