Why report-writer logic resists replacement.

The report is the specification

Ask what determines whether an employee appears on a headcount report and you will usually be pointed at the report. Not at a document describing it — at the job that produces it. The eligibility rule, the effective-date window, the treatment of someone who transferred mid-period: all of it exists as procedural code, accreted over decades, with the original authors long retired.

This is why "we'll rewrite it in SQL" underestimates the work. The rewriting is not the problem. Knowing what to rewrite is.

What is actually buried in there

• Eligibility and exclusion. Which records count, which are suppressed, and the special cases — terminated but still paid, on leave, dual appointment — each added in response to something that went wrong once.
• Proration and apportionment. Splitting a value across departments, funds or periods, with a rounding rule that quietly decides who absorbs the remainder.
• Retroactive adjustment. Corrections applied to a closed period, and the decision about whether they land in the period they belong to or the period they were entered.
• Rounding. Not a detail. Half-adjusting at each step rather than rounding once at the end produces different totals, and in a statutory report that difference is a discrepancy somebody has to explain.

Sequence dependence versus set logic

This is the deepest mismatch. A report writer walks a sorted file one record at a time, carrying state: accumulating totals, watching for a control break when a key changes, emitting subtotals, resetting accumulators, moving on. The shape of the output is a consequence of the order of the input.

SQL is set-based. It has no inherent notion of "the previous record" or "the moment the department changed". Window functions and grouping sets express most of it, but the translation is not mechanical, and the places it is least mechanical are exactly where the business rules hide: what happens at a break when the group is empty, how a total behaves when a subordinate value is negative, whether a suppressed detail line still contributes to the total above it.

A conversion that reproduces the arithmetic but not the break behaviour gives you a report that is right in aggregate and wrong on the page.

"Close enough" is not a category

For compliance and statutory reporting there is no tolerance band. A workforce report filed with a regulator either reconciles to the system of record or it does not. That constraint is useful, because it makes the acceptance criterion unambiguous: output parity, line for line, against the existing report, across a period long enough to contain the awkward cases.

How to actually test it

Run both. Keep the legacy job running and produce the new output alongside it for the same periods, then compare mechanically rather than by inspection. Parity across one clean month proves very little. The value is in the months containing a fiscal-year boundary, a retroactive correction, a mid-period transfer, a terminated employee with a final payment.

Every divergence is then one of two things: a defect in the new logic, or a rule nobody knew about. Both are worth finding, and the second is why this approach pays for itself — it is how the undocumented rule finally gets written down.

Only once parity holds across those cases does switching off the original become a decision rather than a gamble. The old job is cheap to keep running for another quarter. Discovering in front of an auditor that the new one treats retroactive adjustments differently is not.