Creating SQL Reports Based on Date Criteria

HACK 21 Report on Any Date Criteria

To report performance indicators you need to generate values for specific time periods. Business analysts commonly are interested in the current month compared to the preceding month, or the corresponding period in the preceding year. You can do all of this in SQL.

In the examples that follow, the original data is in a table,t. This table records individual incidents of paperclip usage. Every row contains the date (whn) and the number of paperclips used (v):

  mysql> SELECT * FROM t;
  +------------+------+
  |whn         | v    |
  +------------+------+
  |2006-01-07  |   53 |
  |2006-01-13  |   46 |
  |2006-01-18  |   99 |
  |2006-01-19  |   15 |
  |2006-01-26  |    9 |
  ...