“Send this email exactly 23 business days after the customer’s payment due date.” Sounds like a straightforward automation requirement. A scheduled query, a date filter, done.

Add weekends. Add public holidays. Add the requirement that if today is a non-working day, the automation must not skip those customers — it must compensate on the next working day by catching everyone who was due on the skipped days. Now it’s a different problem entirely.

This was the real challenge a financial services client brought to us. Their payment overdue reminders needed to respect business days — not calendar days — and they needed a solution that was fully automated, required no manual intervention, and adjusted dynamically every time a weekend or holiday cluster changed the effective send date.

Calendar days and business days are not the same. A payment overdue reminder that ignores weekends and holidays either sends on the wrong day or silently misses customers.

Why SQL Alone Can’t Solve This

The naive SQL approach is: WHERE DaysPastDue = 23. This works on normal weekdays. But on a Tuesday after a 3-day holiday weekend (Saturday + Sunday + Monday public holiday), you need to catch everyone who was at 23 days on Saturday, Sunday, and Monday — not just Tuesday.

That means your query on Tuesday needs: WHERE DaysPastDue IN (23, 24, 25, 26). And the range changes every day, depending on how many non-working days preceded the current send date. Hardcoding this in SQL creates a maintenance nightmare. You’d need to manually update the query every time a holiday cluster occurs.

The better architecture: let SSJS calculate the dynamic range, then pass it to SQL. SSJS has native JavaScript date functions that can detect weekends in real time, and it can query a holiday reference Data Extension to check for public holidays. The SQL stays clean; the intelligence lives in the script.

The Three-Part Solution

Part 1: Holiday Reference Data Extension

A simple Data Extension maintained annually by the business team, listing all public holidays for the year. The SSJS script reads this DE on every run — it never hardcodes holidays, it looks them up dynamically.

HolidayDate    Date     (Primary Key)
HolidayName    Text
Region         Text     (optional, for multi-region support)

-- Example records:
2026-01-26  "Republic Day"       "IN"
2026-03-29  "Good Friday"        "IN"
2026-08-15  "Independence Day"   "IN"

Part 2: The SSJS Counter Script

This script runs as the first activity in the daily Marketing Cloud automation. It checks whether today is a weekend or holiday. If it is, it increments a counter and exits — no send runs today. If it’s a working day, it reads the counter, builds the DaysPastDue range dynamically, triggers the SQL query with that range, and resets the counter to zero.

<script runat="server">
Platform.Load("Core", "1");

var today      = new Date();
var dayOfWeek  = today.getDay();        // 0=Sun, 6=Sat
var isWeekend  = (dayOfWeek === 0 || dayOfWeek === 6);

// Check holiday DE
var todayStr   = Platform.Function.FormatDate(today, "yyyy-MM-dd");
var holidays   = Platform.Function.LookupRows("HolidayCalendar",
                                               "HolidayDate", todayStr);
var isHoliday  = (holidays && holidays.length > 0);

// Read current counter
var cRows  = Platform.Function.LookupRows("SkipDayCounter",
                                           "CounterKey", "MAIN");
var count  = (cRows && cRows.length > 0)
             ? parseInt(Platform.Function.Field(cRows[0], "CounterValue"))
             : 0;

if (isWeekend || isHoliday) {
    // Skip today — increment counter
    Platform.Function.UpsertDE("SkipDayCounter",
        ["CounterKey"], ["MAIN"],
        ["CounterValue"], [count + 1]);
} else {
    // Working day — build range and trigger send
    var daysRange = [];
    for (var i = 0; i <= count; i++) { daysRange.push(23 + i); }

    // Write range to QueryParam DE for SQL to pick up
    Platform.Function.UpsertDE("QueryParams",
        ["ParamKey"], ["DAYS_PAST_DUE"],
        ["ParamValue"], [daysRange.join(",")]);

    // Reset counter
    Platform.Function.UpsertDE("SkipDayCounter",
        ["CounterKey"], ["MAIN"],
        ["CounterValue"], [0]);
}
</script>

Part 3: The Dynamic SQL Query

The SQL query reads the DaysPastDue range written by the SSJS script and filters contacts accordingly. On a normal day, it reads 23. After a 3-day holiday weekend, it reads 23,24,25,26. The SQL itself never changes — only the parameter does.

No customer is missed because of a holiday. The counter ensures Monday compensates for Saturday, Sunday, and any public holidays in between.

Walking Through a Real Scenario

Let’s make the counter logic concrete with a real example spanning a 4-day holiday cluster (Thursday national holiday + Friday national holiday + Saturday + Sunday).

Wednesday (normal working day): Counter = 0. Query runs for DaysPastDue = 23. Counter resets to 0.

Thursday (national holiday): Counter increments to 1. No send.

Friday (national holiday): Counter increments to 2. No send.

Saturday (weekend): Counter increments to 3. No send.

Sunday (weekend): Counter increments to 4. No send.

Monday (working day): Counter = 4. Query runs for DaysPastDue IN (23, 24, 25, 26, 27). Monday’s single run catches everyone due over the entire 5-day period. Counter resets to 0.

Frequently Asked Questions