We're in a bit of a jam , an email to the support desk read, we accidentally ran an entire day's worth of transactions for 11 Oct 2009 instead of 11 Oct 2006. Can you fix this?

In the world of retail, it's not an uncommon practice to "open" for a business date that is not the current date. Think of 24-hour stores that want to "close" the day at 11:00 PM instead of midnight, or the cases when the registers are out of commission. Whatever the reason, it's a feature that customers want and a feature that T. Ferguson's company provides in their point-of-sale systems.

Obviously, there's no way for the software to know if a different date is purposeful or accidental; all it can do is default the "open" date to the current date and hope that someone would notice a mistake on the registers, receipts, etc. before the day was "closed" out. The support email was the first "problem" that T.'s company had with this feature since first offering fifteen years ago.

Despite having a nation-wide chain of stores, with each bringing in nearly $500,000/day in sales, this company decided not to go for the extended-hours support contract. With no one to call at 9:30 PM for support, the shift manager ignored the incorrect date and "closed out" the store's point-of-sale system. He left a note for the general manager, who promptly emailed support the next morning.

The general manager also called the support line at 9:01 AM -- just after it opened -- to make sure they got the email. He was very concerned that the error would gravely impact their October reports, forecasting reports, inventory, and just about anything else that relied on that day's transactional data. The support rep assured the general manager that the development team was working on a way to fix the issue.

From a programming perspective, this was actually an easy thing to fix. All of the daily transactions are stored in a single database table, so a simple UPDATE script and a "re-close" should do the trick. They reproduced the "problem" on a test machine, ran the fix script, and watched it worked like a charm. T. called up the store to let the manager know how they planned to resolve the issue.

"But," the manager asked, "what about when someone makes a return? Their printed receipt will have a different transaction date. Won't the register refuse the return?"

"Nope," T. replied, "we only use the store number, register number, and transaction number when we validate the receipts for returns."

"Sounds great," the manager said in a much less stressful tone, "what a relief! I was really worried about how bad this would be."

The fix was sent to a technician to fix the problem on site. Before running the script, he noticed one thing that the development team missed: not only was there only one day of faulty data in the database, there was only one day. Period. All the transactional history was gone!

That, of course, would present a problem when trying to process a return. Or receiving merchandise that was ordered in the past. Or verifying an employee's time clock punches. Or tracking special-ordered items. Or knowing whether the store is on pace to meet its weekly sales goal. Or just about any activity of any consequence in retail that ISN'T selling merchandise.

The technician reported this back to the development team. After a bit of digging, they figured out why only the one day of data was left: part of the register closing code purges data that's over three years old. And how does one find three-year old data when the system clock is not a reliable indicator? Why, by taking the business date of the newest transaction and subtracting three years, of course!

The manager's day was about to get much, much worse.

