Intermittent leave, counted to the entitlement

A continuous leave is one interval: out on a date, back on a date, and the workers-on-leave page models it cleanly. Intermittent leave is the opposite shape. An hour for a recurring appointment, a day for a flare, taken in pieces across months, each piece drawn against a fixed entitlement, usually 480 hours in a rolling 12-month window.

That combination, many small usages against a cap measured on a sliding window, is exactly what delivered absence tracking handles worst. The balance is not a simple accrual, the window is not the calendar year, and the compliance question, how much protected leave remains, has to be right per worker on any given day.

Each row is one subtraction: entitlement minus used equals remaining, checked by the same battery that ties the money pages. W-1102, at 12 hours remaining, is the row that matters: a worker nearly out of protected leave, and the conversation a manager must have before the next request, not after. The used column is the sum of many intermittent pieces over the trailing year, not a calendar total. Sample values are illustrative, never client data.

Model each piece as a usage event, then compute the used total with a window function over the trailing twelve months, per worker, on any date. Remaining is the entitlement minus that rolling sum. The near-exhaustion flag becomes a filter, and the audit answer, why did this worker have this much protected leave on that date, is a query with the pieces listed beneath it.

-- protected leave used, rolling 12 months, per worker, as of a date
SELECT worker_key,
       480 AS entitlement_hours,
       SUM(hours) AS used_rolling_12mo,
       480 - SUM(hours) AS remaining
FROM fct_absence_event
WHERE leave_type = 'protected_intermittent'
  AND event_date > DATE '2026-05-31' - INTERVAL '12' MONTH
  AND event_date <= DATE '2026-05-31'
GROUP BY 1 ORDER BY 4

The same rolling-window discipline the turnover denominator and the hours lookback use, applied to a statutory entitlement. This is the compliance and audit answer delivered tracking cannot hold still, and the reason it belongs in a model you own.