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:

Column Type Comments
id int NOT an autonumber/identity, but still the PK
Name varchar  
Applies_OT bit  

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

id Name Applies_OT
1 Regular Yes
13 Jury Duty No
14 Bereavement No
100 Overtime Yes
300 Sick No
801 Vacation Yes
805 Holiday Yes

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,
  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,
    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 =
  LEFT OUTER JOIN Pay_Types AS ptb ON (b.Pay_Type =
GROUP BY a.Employee, a.In_Week,, a.In_Day, a.Job_Code, a.Hours, a.Pay_Type, pta.Applies_OT
ORDER BY a.Employee, a.In_Day,

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
66411 Mon 805/HOL 0 0 8 8 8 8 805/HOL
66411 Tue 1/REG 8 8 9 9 17 17 1/REG
66411 Wed 13/JUR 17 17 8 0 25 17 13/JUR
66411 Thu 1/REG 25 17 10 10 35 27 1/REG
66411 Fri 1/REG 35 27 9 9 44 36 1/REG
66411 Sat 1/REG 44 36 6 6 50 42 100/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
66411 Sat 1/REG 44 36 4 4 48 40 1/REG
66411 Sat 100/OVT 48 40 2 2 50 42 100/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.

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.