Home arrow SQL arrow Creating SQL Reports Based on Date Criteria

Creating SQL Reports Based on Date Criteria

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.

Author Info:
By: O'Reilly Media
Rating: 4 stars4 stars4 stars4 stars4 stars / 18
December 20, 2007
  1. · Creating SQL Reports Based on Date Criteria
  2. · Monthly Totals
  3. · Current Month
  4. · Year-to-Date Totals

print this article

Creating SQL Reports Based on Date Criteria
(Page 1 of 4 )

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 |

blog comments powered by Disqus

- Focusing SQL Queries
- Complex SQL Queries
- A Close Look at the SQL Query
- Generating Reports with SQL Date Handling
- Creating SQL Reports Based on Date Criteria
- SQL Date Handling and Data Trends
- Date Handling
- Introduction to SQL
- Lies, Damn Lies, Statistics, and SQL

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials