The Payroll Month Starts on the Sixteenth

Ask a developer when a month starts and they will say the first. Ask a payroll department and the answer is whatever the pay cycle says — and in this company, the pay cycle runs from the 16th of one month to the 15th of the next. That single business fact, entirely reasonable and entirely non-negotiable, ripples through the estate's reporting SQL in ways that produce some of its densest and strangest code: twelve correlated subqueries in one procedure, weeks deliberately capped at five days, durations averaged by casting time to float and back, and contractor-exclusion patterns that drift by a single digit between one proc and its copy. This part reads the payroll-shaped calendar maths, because the org-chart procs of part 14 were the schema's computer science; this is its accountancy.

A month that isn't a month

GetYearOverView produces a twelve-month summary for an employee or team — hours, presence, completion — one figure per business month. Because the business month is the 16th-to-15th window, the procedure cannot use MONTH(LogDate); a single calendar month contains halves of two business months. So it takes a list of month tokens and, for each, computes a bespoke date range and runs a subquery against it:

-- shape only: one correlated subquery per month token
SELECT
  (SELECT AVG(...) FROM dbo.ShiftDetailMonthlyReport r
   WHERE r.EmployeeId = @EmpId
     AND r.LogDate >= @jan16prev AND r.LogDate <= @jan15) AS JanFigure,
  (SELECT AVG(...) FROM dbo.ShiftDetailMonthlyReport r
   WHERE r.EmployeeId = @EmpId
     AND r.LogDate >= @feb16prev AND r.LogDate <= @feb15) AS FebFigure,
  -- ... ten more, one per month, boundaries shifted to the 16th/15th ...

Twelve near-identical correlated subqueries, one per month, each scanning the fact table for its own 16th-to-15th slice. Read charitably, it is a hand-rolled pivot: the shape of the output (one column per month) drove the shape of the query (one subquery per month), and with no PIVOT-friendly month key available — because the month key is computed, not stored — twelve copies was the path of least resistance. Read honestly, it is twelve table scans where a single GROUP BY over a derived month expression would do one, and it is twelve places a boundary bug can hide independently.

The durable point sits underneath the SQL: the business month was a computed concept the schema never modelled. Had a PayrollMonth column (or a proper date dimension with a payroll-period attribute) existed on the fact table, every one of these procedures would collapse to an ordinary GROUP BY PayrollMonth. Instead the 16th-to-15th rule lives re-derived, inline, in every proc that needs it — an unmodelled dimension paid for over and over in string-shaped date arithmetic.

Averaging a duration by turning it into a float

The measures being averaged are durations stored as SQL time — office hours, productivity — and time does not average. AVG wants a number; time is not one. The estate's workaround is a cast sandwich:

CAST(CAST(AVG(CAST(CAST(OfficeHours AS DATETIME) AS FLOAT)) AS DATETIME) AS TIME)

Peel it from the inside out: time becomes datetime (an instant on day zero), then float (a fractional day-number that is averageable), AVG runs, and the result is cast back through datetime to time. It works — a duration is a fraction of a day, and averaging fractions of a day is legitimate — but four nested casts to average a column is a loud signal that the column's type is fighting its use. As part 11 argued, these measures wanted to be integers of seconds or minutes; stored that way, the average is AVG(OfficeHoursSeconds) and the cast tower vanishes. The tower is not a bug — it is the interest payment on a type decision made upstream.

Five-day weeks and the moving denominator

The dashboard's utilisation figure is where the calendar maths turns into a genuine analytics hazard, and it previews part 16 directly. Utilisation is worked hours over expected hours, bucketed by week — and a week's expected hours are capped at five days:

... / (DailyTargetTime * IIF(COUNT(DISTINCT LogDate) > 5, 5, COUNT(DISTINCT LogDate)))

The IIF(... > 5, 5, ...) caps the working-day count at five, so a week where someone worked Saturday doesn't post over 100% utilisation. Defensible. But look at what the cap does to the denominator: it is computed from the days that appear in the data, which — thanks to the dense date spine from part 6 and the sentinel rows that fill it — is not the same population the numerator sums over. Combine that with the sibling filters that ride these procs — OfficeHours != '00:00:00' excludes zero-hour days from the average but not from the count, and an OrgLevel LIKE '%X%' exclusion carves out executives via temp tables — and the denominator of “the same metric” quietly shifts depending on which filter fired. Same label on the dashboard, different divisor underneath.

The contractor filter that drifts by a digit

The finest artefact in the payroll SQL is a copy-paste bug you can see only by diffing two procedures. Contractors and test accounts are excluded from the averages by a pattern match on the employee ID — contractor IDs share a leading-digit convention — expressed as a bracketed LIKE:

-- one variant
WHERE EmployeeId NOT LIKE '%7[0-9][0-9][0-9][0-9][0-9]'   -- five bracket groups

-- the manager/team-lead variant, pasted and drifted
WHERE EmployeeId NOT LIKE '%7[0-9][0-9][0-9][0-9]'        -- four

The employee-facing proc matches a six-digit contractor pattern; the manager and team-lead variants — near-verbatim copies of it — match a five-digit one, one bracket group short. The two procs therefore exclude different sets of people from the same company-wide metric. An employee whose ID is caught by one pattern and missed by the other is counted in a manager's average and absent from their own, or vice versa. Nobody would ever notice from the output; both numbers look plausible, and only a character-by-character diff of two 200-line procedures reveals that they disagree about who the contractors are.

This is copy-paste as a correctness failure, not merely a maintenance one. The estate's habit — four dashboards, five report-building blocks, three export paths — usually costs duplication tax. Here it cost a silent divergence in a headline metric, encoded as one missing [0-9]. When a magic pattern is copied instead of shared, every copy is free to drift, and the drift hides in the one place nobody diffs: production SQL that returns a plausible number.

The whole procedure is riddled with these digit-level fragilities because the calendar logic is expressed as string surgery — dates concatenated, split, and reassembled rather than computed with date functions — but the contractor pattern is the one where a single digit changes who gets counted, which is a different and worse category of wrong.

Which brings the series to its analytical climax, foreshadowed in every proc that computed an “average”: the platform's headline KPIs are not computed one way. The same dashboard tile is fed by different formulas depending on which role's procedure rendered it — a plain mean here, a five-day-capped weekly utilisation there — with no single definition anywhere. Next, one metric, three answers.