Workers on Leave

Who is out, who is due back, and who is overdue. A leave is an interval with an expected end and an actual one, and the report is only right if the model stores it that way.

RuleLand leaves as intervals: start, expected return, actual return. On-leave as of a date is one BETWEEN, overdue is one comparison.Nevercount workers on leave out of headcount. They are active workers; the headcount report already includes them.

◆ The traps— intervals not statuses, two return dates, pending events, and what the leave type controls.

Trap	What goes wrong
Status fields lie about dates	An on-leave flag says who is out today and nothing else. Leave start and return land as events on effective dates; the model derives intervals from them, and any past or future date becomes answerable.
Two return dates	Expected return plans coverage; actual return closes the interval. An expected date in the past with no return event is the overdue case, and it is a follow-up list, not a data error.
Pending events	Requested leaves awaiting approval are visible before they start. Publish approved and approved-plus-pending as two labeled views, the same rule the balance report uses.
The type controls the effects	Whether accruals continue, pay continues, and benefit deductions continue is set per leave type. Land the effect flags on dim_leave_type; the accrual and arrears behavior downstream reads them instead of guessing.
Intermittent leave	Some leaves are taken in broken stretches under one approval. One parent interval, child segments beneath it; counting segments as leaves overstates everything.

◆ The owned model, and the SQL— intervals from the event fact, one query for out-now and overdue.

Leave starts and returns are rows in fct_worker_event; the model pairs them into intervals with the leave type's effect flags joined on. As of a date:

-- on leave as of May 31, with the overdue flag
SELECT w.worker_id,
       lt.leave_type,
       l.leave_start,
       l.expected_return,
       CASE WHEN l.actual_return IS NULL
             AND l.expected_return < DATE '2026-05-31'
            THEN 'Overdue' END AS status
FROM fct_leave_interval l
JOIN dim_worker w      ON l.worker_key = w.worker_key AND w.is_current
JOIN dim_leave_type lt ON l.leave_type_key = lt.leave_type_key
WHERE l.leave_start <= DATE '2026-05-31'
  AND (l.actual_return IS NULL OR l.actual_return > DATE '2026-05-31')
ORDER BY l.expected_return

Sample output, May 31:

worker_id	leave_type	leave_start	expected_return	status
W-0733	Medical	2026-04-06	2026-05-18	Overdue
W-1101	Parental	2026-03-02	2026-06-22
W-0489	Parental	2026-05-04	2026-08-24
W-1210	Personal, unpaid	2026-05-11	2026-06-08

Four workers out, all inside the headcount report's 147 active, the rulebar's Never in practice. W-0733 is the follow-up: expected back May 18, no return event. W-1210's unpaid type means paused deductions accumulating as arrears for the benefits reconciliation. Sample values are illustrative, never client data.

Want leave visibility that runs itself?

Intervals from the event fact, effect flags per leave type, and the overdue list, reconciled against Workday, and you own every line.

Talk to us

Terms on this page

leave interval: Start, expected return, actual return. The unit the model stores.
expected return: The planning date. Coverage and staffing read it.
actual return: The event that closes the interval.
overdue: Expected return passed, no return event. A follow-up list.
effect flags: Per leave type: accruals continue, pay continues, deductions continue.
intermittent leave: One approval, broken stretches. Parent interval, child segments.
pending leave: Requested, not yet approved. The second labeled view.