Who is on leave, as of any date

Today’s on-leave list is easy. Last quarter’s, for the auditor, or the trend of leave load by department, needs intervals: who entered leave, who returned, who is past their expected date. Delivered lists show now; intervals answer when.

RuleModel leave as intervals from events: a start, an expected return, an actual return. Every question is a date inside or outside an interval.

Nevertrust an on-leave count that cannot state its date. A list without an as-of is this morning’s screen, remembered optimistically.

Today’s list is easy. The auditor’s date, the trend, and the overdue list all need intervals.

◆ The report, May 31, 2026on leave, by department, overdue named.

Department	On leave	Overdue return
Sales	4	1
Technology Delivery	3	0
Support	2	1
Total, reconciles to headcount	9	2

Nine of the headcount page’s 351 are on leave at month end, and the two past their expected return are named on the report with owner and days overdue, because an overdue return is a conversation, not a count. Sample values are illustrative, never client data.

◆ Intervals, not listswhy the event fact answers what screens cannot.

The delivered view is a list of current leave states. The event fact stores each leave as an interval: entered, expected back, actually back. Any as-of question is a date inside intervals; leave load is intervals crossing each month; return reliability is expected against actual. The two return dates practitioners juggle become two columns instead of two arguments.

-- on leave as of any date, from intervals
SELECT w.department_name, COUNT(*) AS on_leave
FROM fct_absence_event e
JOIN dim_worker w ON e.worker_key = w.worker_key
WHERE e.event_type = 'leave'
  AND e.leave_start <= DATE '2026-05-31'
  AND COALESCE(e.actual_return, DATE '9999-12-31') > DATE '2026-05-31'
GROUP BY 1 ORDER BY 2 DESC

◆ What coverage planning getsthe questions after the roster.

With intervals stored, the follow-on questions are groupings: leave load by month by department for capacity planning, average leave duration by type, and the return-date reliability that tells you whether expected dates mean anything. Accrual impact during leave, the ambiguity practitioners report per leave type, becomes an explicit flag on the interval instead of a surprise on the balance.

Use case

Problem

On-leave lists show only today, return dates live in two fields nobody reconciles, and leave load history does not exist.

What we build

Leave modeled as intervals in the event fact: entered, expected back, actually back, with accrual flags.

What you get

The roster as of any date, the overdue list with names, and leave load trends for capacity planning.

Who is overdue to return, right now?

We ship the interval model and the roster that names them.

Talk to us

Terms on this page

interval: A leave with its start, expected return, and actual return.
on-leave state: What the delivered list shows: current, dateless.
overdue return: Past the expected date with no actual return recorded.
leave load: How many intervals cross a period. The capacity number.
return reliability: Expected against actual return dates, over time.
accrual flag: Whether hours accrue during this leave. Explicit on the interval.
as-of date: The past date a question is asked about, as the data stood then.