Time-Off Balances & Liability

The hours workers have banked, and what they cost. Both numbers move on rules the balance screen hides: when accruals post, which plan year applies, and whether a negative balance is an error or a policy.

RuleLand the accrual, usage, and adjustment lines; derive every balance in the warehouse, as of period endsNeverfloor a balance at zero or blend plans with different plan years. Negatives and mixed year-ends are real.

◆ Why balance reports disagree— accrual timing, plan years, pending requests, and legitimate negatives.

Trap	What goes wrong
Accruals post at period end	Balances update on the last day of the pay period. A mid-period as-of date silently excludes the current accrual, so two reports run a week apart disagree by one period's earn. Report on period-end dates.
Plans have their own years	Vacation can run the calendar year while sick runs a fiscal year and a third plan its own cycle. Resets, carryover, and forfeiture are per plan; a company-wide reset date does not exist. The plan year lives on the plan.
Two balances per worker	Some views include approved future requests and pending events, some show only posted usage. Both are legitimate: posted, and posted including pending. Publish both, labeled.
Negative balances are real	Use-before-accrue policies and pending donation credits produce negatives by design. A model that floors at zero understates usage and misstates liability.
Life changes adjust accruals	FTE changes, unpaid status, and leaves change the earn rate mid-year, and a job change with an eligibility change can require a manual adjustment. Land adjustments as their own line type; they are corrections, not noise.
Carryover has rules	Caps, forfeiture, and overflow rerouting into other plans happen at each plan's year boundary. The year-over-year audit is closing balance against opening plus documented carryover, per plan.

◆ The owned model, and the SQL— a line-grain time-off fact, balances derived, liability in dollars.

fct_time_off lands one row per accrual, usage, or adjustment line per worker, plan, and period, the same never-extract-balances discipline as Payroll Result. Liability prices the derived balance at current rates:

-- period-end balances per plan, priced into liability by cost center
WITH balance AS (
  SELECT f.worker_key,
         SUM(f.hours) AS balance_hours
  FROM fct_time_off f
  JOIN dim_date d ON f.date_key = d.date_key
  WHERE d.full_date <= DATE '2026-05-31'
    AND f.plan_year = 2026
  GROUP BY 1
)
SELECT cc.cost_center_name,
       SUM(b.balance_hours)                                 AS hours,
       ROUND(SUM(b.balance_hours * w.hourly_cost_rate), 0)  AS liability
FROM balance b
JOIN dim_worker w       ON b.worker_key = w.worker_key AND w.is_current
JOIN dim_cost_center cc ON w.cost_center_key = cc.cost_center_key
GROUP BY 1
ORDER BY 3 DESC

Sample output, May period end, the cost centers from the mapped dimension, at average rates of 65.00, 60.00, and 55.00:

cost_center_name	hours	liability
Field Sales West	6,840	444,600
Sales Ops	2,120	127,200
Marketing	1,910	105,050

Liability is a balance-sheet number, so it reconciles like one: the movement between two period ends must equal accruals minus usage plus adjustments for the window, per plan, and the model shows that walk. Sample values are illustrative, never client data.

Want a liability number finance signs off on?

The line-grain fact, per-plan years, and the balance walk between periods, reconciled against Workday, and you own every line.

Talk to us

Terms on this page

time-off plan: One accruing balance with its own rules and plan year. Workers hold several.
accrual: Hours earned, posted at the end of each pay period.
plan year: The plan's own reset cycle. Calendar for one plan, fiscal for another.
carryover: Hours crossing the plan-year boundary, subject to caps and forfeiture.
pending balance: Posted balance plus approved future and awaiting-approval requests. The second legitimate number.
negative balance: Use-before-accrue or pending credits. Policy, not error.
adjustment: A manual or rule-driven correction line. Landed as its own type.
liability: Balance hours priced at current rates. A financial-statement number.
balance walk: Opening plus accruals minus usage plus adjustments equals closing. Per plan, per period.