Home arrow SQL arrow Creating SQL Reports Based on Date Criteria
SQL

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
TABLE OF CONTENTS:
  1. · Creating SQL Reports Based on Date Criteria
  2. · Monthly Totals
  3. · Current Month
  4. · Year-to-Date Totals

print this article
SEARCH DEVARTICLES

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
SQL ARTICLES

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

Developer Shed Affiliates

 




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