Pattern

Counting business days

Build the working days once (no weekend, no holiday), then count them between two dates.

generate_series over the period, EXTRACT(ISODOW FROM day) <= 5, and NOT EXISTS a holiday: that is the calendar. Count its days with day > start AND day <= end. The shortcut "weekdays minus holidays" subtracts twice a holiday that falls on a weekend. Real projects keep the calendar as a table, with is_business_day per country.

WITH days AS (
  SELECT CAST(d AS DATE) AS day
  FROM generate_series(DATE '2025-12-22', DATE '2025-12-31', INTERVAL '1 day') AS t(d)
),
holidays(day) AS (VALUES (DATE '2025-12-25'))
SELECT COUNT(*) AS calendar_days,
       SUM(CASE WHEN EXTRACT(ISODOW FROM day) <= 5
                 AND day NOT IN (SELECT day FROM holidays) THEN 1 ELSE 0 END) AS business_days
FROM days;

calendar_days	business_days
10	7

calendar_days

business_days

Try it in Randori

Practise:Business days to pay