I have a client is big into reporting. Occasionally, I’ll get a pretty interesting request that makes total sense in the real world but doesn’t translate well to database query.<\/p>\n
Today, that request was to produce a report to show holiday sales trends. Okay, I first thought that we’d just do a daily comparison of sales numbers, showing this years sales in one column and last year’s sales in column number two.<\/p>\n
But then I thought about it. We might want to compare how Black Friday 2014 outperformed Black Friday 2013. Since this “holiday” falls on a different day each year, this report is not exactly cut and dry.<\/p>\n
So, like any good programmer should, I first Google’d for solutions. Though other developers shared their related experience, nothing match my exact requirements.<\/p>\n
Some used a complex stored procedure that followed “holiday rules” to produce an exact day given a year and the name of a holiday. This sounded appealing but it was complex and, I thought, difficult to maintain if we want to add more holidays.<\/p>\n
I decided to go with a flat “cross-reference” table, which would simple have a field for the name of a holiday, and another field for the date. This would require manual entry of the holiday names and dates that you want to track, but in my case, it was only a dozen per year.<\/p>\n