MySQL Sales Reporting by Holiday

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)

Why follow me on Twitter?

  • I tweet about new technologies, services or libraries I find interesting
  • Yeah, sometimes I'll post a pet-peeve or rant about something trivial
  • If I discover something that made my web development life easier, I share it
  • I'll shout out any handy tip that I think might be useful to other devs


Tagged .

Updated: 2014-12-04

Phil LaNasa follow us in feedly