Anatomy of a bug: Tricky Overtime, Part 1

Table of contents for tricky_overtime

  1. Calculating overtime with pure SQL
  2. Anatomy of a bug: Tricky Overtime, Part 1

I am not perfect. I occasionally write software that has showstopper-level bugs. I was recently presented with a bug in my payroll overtime-calculating software. I figured I’d dissect the bug here in my blog so that others might learn from my mistakes.

First, the bug isn’t in the code that I wrote in my previous entry — that code works just fine. After putting that system in place, my company’s overtime policy changed such that only certain pay types counted towards overtime. That is, while holidays and vacation days might count towards your 40 pre-overtime hours, jury duty and bereavement leave would not. Thus, a new table was born, Pay_Types:

ColumnTypeComments
idintNOT an autonumber/identity, but still the PK
Namevarchar 
Applies_OTbit 

Our system’s pay types include, among other things:

idNameApplies_OT
1RegularYes
13Jury DutyNo
14BereavementNo
100OvertimeYes
300SickNo
801VacationYes
805HolidayYes

I tackled this by extending my original self-joined rolling-total query to keep two counts: total hours and hours towards overtime:

SELECT a.In_Week, a.Employee, a.In_Day, a.Job_Code,
  a.Pay_Type AS Orig_Pay_Type,
  COALESCE(SUM(b.Hours),0) AS HoursBefore,
  COALESCE(SUM(COALESCE(ptb.Applies_OT,0) * b.Hours),0) AS HoursBefore_Toward_OT,
  a.Hours,
  COALESCE(pta.Applies_OT,0) * a.Hours AS Hours_Toward_OT,
  COALESCE(SUM(b.Hours),0) + a.Hours AS HoursAfter,
  COALESCE(SUM(COALESCE(ptb.Applies_OT,0) * b.Hours),0) + (COALESCE(pta.Applies_OT,0) * a.Hours) AS HoursAfter_Toward_OT,
  CASE
    WHEN (pta.Applies_OT > 0) AND (a.Pay_Type < 10) AND (COALESCE(SUM(COALESCE(ptb.Applies_OT,0) * b.Hours),0) + (COALESCE(pta.Applies_OT,0) * a.Hours) > 40.0) THEN 100
    ELSE COALESCE(a.Pay_Type,1)
  END AS New_Pay_Type
FROM TimeEntry_Log AS a LEFT OUTER JOIN TimeEntry_Log AS b
    ON (a.Employee = b.Employee) AND (a.In_Week = b.In_Week)
	AND (b.Hours IS NOT NULL) AND (b.In_Date < a.In_Date)
  LEFT OUTER JOIN Pay_Types AS pta ON (a.Pay_Type = pta.id)
  LEFT OUTER JOIN Pay_Types AS ptb ON (b.Pay_Type = ptb.id)
WHERE (a.Hours IS NOT NULL)
GROUP BY a.Employee, a.In_Week, a.id, a.In_Day, a.Job_Code, a.Hours, a.Pay_Type, pta.Applies_OT
ORDER BY a.Employee, a.In_Day, a.id

Yuck. Let’s take it apart, starting with the middle.

Our base join hasn’t changed: we’re still joining the time entry log table with itself to get a running total. We’ve added our Pay_Types table in twice, once to each of the log tables. I did make a slight addition for some extra null-checking to weed out people that forgot to clock out.

Moving back up to the top, we can see that I left the first few columns the same. The pay type on the left side of the join becomes the original pay type. We’ll need this later to keep track of if the pay type has changed.

The next thing we’ve added is the HoursBefore_Toward_OT, which is exactly what it sounds like: the hours that have come before this entry that apply towards overtime. The concept is the same, sum the hours from the right side, but we threw in an extra twist. We multiply the hours by the overtime flag. If the hours don’t apply towards overtime then they end up as zero, as the overtime flag will be false and therefore zero. Yes it’s cheating, and no it won’t work if the TRUE value in your database system isn’t 1. The next change is basically the same trick again, but this time for the current entry’s hours, just as the column after that just combines the previous two.

The last major change is the new CASE statement. It reads: when the current entry applies towards overtime, and the pay type can be promoted to overtime (less than 10), and the HoursAfter_Toward_OT sum is more than 40 hours, then promote the current entry to overtime by setting the New_Pay_Type to the code for overtime, otherwise just leave it the same. We want to count things like Vacation and Holiday pay towards overtime, but we don’t want to promote them or change the pay type. That is, while Vacation and Holiday may count towards overtime, you’ll never get time-and-a-half while sunning yourself on the beach.

Here’s an example:

Employee In Day Orig Pay Type Hours Before Hours Before Toward OT Hours Hours Toward OT Hours After Hours After
Toward OT
New Pay Type
66411Mon805/HOL008888805/HOL
66411Tue1/REG889917171/REG
66411Wed13/JUR171780251713/JUR
66411Thu1/REG2517101035271/REG
66411Fri1/REG35279944361/REG
66411Sat1/REG4436665042100/OVT

Our example worker had a busy week. Monday was a holiday. He worked all day Tuesday. Wednesday he had jury duty. Thursday and Friday were again long days, then he came in for a shorter day on Saturday. How much overtime should he have gotten?

According to our logic, where we promote if the hours after towards overtime are more than 40 and the pay type is promotable, that last Saturday line should be split into two lines, one with overtime and one with regular time:

Employee In Day Orig Pay Type Hours Before Hours Before Toward OT Hours Hours Toward OT Hours After Hours After
Toward OT
New Pay Type
66411Sat1/REG44364448401/REG
66411Sat100/OVT4840225042100/OVT

So, while our worker technically punched 50 hours, he only gets 2 overtime hours because 8 hours earlier in the week were spent at jury duty, which doesn’t count towards overtime. He still gets paid for the hours, just not at time-and-a-half.

Did you spot the bug?

If you didn’t catch it, don’t feel bad. It’s tricky and is a good example of why scope creep is a Bad Thing and why bolting even seemingly-simple things onto an existing system can sometimes lead to complex problems. I’ll post the entry tomorrow that shows the bug and how to stomp it.

Oh, and here’s a hint: reverse the week.

Published by

Rick Osborne

I am a web geek who has been doing this sort of thing entirely too long. I rant, I muse, I whine. That is, I am not at all atypical for my breed.