Creating SQL Reports Based on Date Criteria

(Page 1 of 4 )

In this third part of a four-part series covering SQL date calculations, you'll learn how to generate a report based on various date criteria. This article is excerpted from chapter four of the book SQL Hacks, written by Andrew Cumming and Gordon Russell (O'Reilly, 2006; ISBN: 0596527993). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

HACK 21 Report on Any Date Criteria

A report may depend on ranges of dates that can be tricky to calculate. Monthly totals are pretty straightforward; but how about current month, last month, and year to date?

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 |
  ...

Next: Monthly Totals >>
 

More SQL Articles
More By O'Reilly Media

Comments On: Creating SQL Reports Based on Date Criteria

· This article is an excerpt from the book "SQL Hacks," published by O'Reilly. We hope...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter four of the book SQL Hacks, written by Andrew Cumming and Gordon Russell (O'Reilly, 2006; ISBN: 0596527993). Check it out today at your favorite bookstore. Buy this book now.