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.
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.
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.
So, like any good programmer should, I first Google’d for solutions. Though other developers shared their related experience, nothing match my exact requirements.
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.
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.
I’ll provide that table structure and data to save you time: Holiday MySQL Table
By doing a simple JOIN from my orders table against the holiday table, I can easily “tableize” a comparison of holiday numbers from year to year:
SELECT h.holiday_name, FORMAT(SUM(order_total), 0) as total,
(SELECT FORMAT(SUM(order_total), 0)
FROM orders
INNER JOIN holiday
ON holiday_date = DATE(order_created)
WHERE holiday.holiday_name = h.holiday_name
AND YEAR(holiday_date) = YEAR(o.order_created - INTERVAL 1 YEAR)
) as yearBeforeTotal
FROM orders o
INNER JOIN holiday h
ON h.holiday_date = DATE(order_created)
WHERE YEAR(order_created) = YEAR(NOW())
GROUP BY DATE(order_created)
ORDER BY DAYOFYEAR(order_created)