Reported vs Paid Reconciliation

Hours entered and hours paid are never quite the same number. The gap has exactly four causes, and a reconciliation that names them per period turns a recurring argument into a checklist.

RuleReconcile per worker per period, and decompose the gap into named buckets that sum exactly: unapproved, comp time in lieu, rounding, late approvals.Neverchase punch-level differences. Rounding is configured; the period grain is where the numbers are meant to agree.

◆ The four causes— each with its size, its direction, and what to do about it.

Cause	Behavior	Action
Unapproved by deadline	The largest bucket. Reported hours that missed the payroll cutoff are unpaid in that run and land in a later one.	The daily pre-deadline control on the Overtime page exists to shrink this bucket to zero before it happens.
Comp time in lieu	Overtime hours banked as time off at premium rates instead of paid. Reported, never paid, and correctly so.	Route the banked hours to the liability report; here they are a named bucket, not a discrepancy.
Rounding	Small, systematic, both directions. Entries round on configured rules before calculation.	Accept it at the period grain and track its net size. A growing rounding bucket means the rules changed, which is worth knowing.
Late approvals	Last period's unapproved hours paid this period. The bucket that reverses.	Show it separately so this period's gap is not blamed on this period's managers.

◆ The owned model, and the SQL— entries on one side, paid hours on the other, buckets that close.

Reported hours come from the landed entries with their approval status; paid hours come from fct_payroll_result. Per org, one period:

-- May, per org: reported, paid, and the gap in named buckets
WITH reported AS (
  SELECT t.org_key, SUM(t.hours) AS reported_hours,
         SUM(CASE WHEN t.approval_status <> 'Approved' THEN t.hours END) AS unapproved,
         SUM(CASE WHEN t.paid_as = 'Comp time' THEN t.hours END)         AS comp_in_lieu
  FROM stg_time_entries t
  WHERE t.period_key = 202605
  GROUP BY 1
),
paid AS (
  SELECT f.org_key, SUM(f.hours) AS paid_hours
  FROM fct_payroll_result f
  JOIN dim_pay_component pc ON f.pay_component_key = pc.pay_component_key
  WHERE f.paid_in_period_key = 202605 AND pc.carries_hours
  GROUP BY 1
)
SELECT o.level_2 AS organization,
       r.reported_hours, p.paid_hours,
       r.reported_hours - p.paid_hours                          AS gap,
       r.unapproved, r.comp_in_lieu,
       r.reported_hours - p.paid_hours - r.unapproved - r.comp_in_lieu AS rounding_net
FROM reported r JOIN paid p USING (org_key) JOIN dim_org o ON r.org_key = o.org_key

Sample output, May, Field Sales West:

organization	reported_hours	paid_hours	gap	unapproved	comp_in_lieu	rounding_net
Field Sales West	24,310	24,000	310	262	36	12

The 24,000 paid hours are the Overtime report's row for the same org and month, and the gap closes: 262 plus 36 plus 12 is 310. The unapproved 262 is next period's late-approval bucket in advance; the 36 comp-time hours are now sitting in the liability balance. When the buckets stop closing, the leftover is a data problem, missing entries or a component misclassified on carries_hours, and that is the finding. Sample values are illustrative, never client data.

Want reported and paid to stop being an argument?

Both sides in one warehouse, four named buckets that close, and the pre-deadline control that shrinks the biggest one, and you own every line.

Talk to us

Terms on this page

reported hours: What was entered, approved or not. The operational side.
paid hours: Hours on committed payroll lines. The financial side.
gap: Reported minus paid, decomposed into four named buckets that sum exactly.
unapproved bucket: Missed the payroll cutoff. Shrinks with the pre-deadline control.
comp time in lieu: Overtime banked as time off. A liability, not a discrepancy.
rounding net: Configured entry rounding, both directions. Tracked, not chased.
late approvals: Last period's unapproved, paid now. The reversing bucket.