Home arrow SQL arrow Page 3 - 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 - Current Month
(Page 3 of 4 )

If you want to see the data for the current month you can test both month and year in theWHEREclause:

  mysql> SELECT * FROM t
      ->  WHERE MONTH(whn)=MONTH(CURRENT_DATE)
     
->    AND  YEAR(whn)=YEAR(CURRENT_DATE)
      ->  ORDER BY whn;
 
+------------+------+
  | whn        | v    |
  +------------+------+
  | 2006-06-07 |  96  |
  | 2006-06-11 |   4  |
  | 2006-06-12 |  78  |
  | 2006-06-12 |  36  |
  | 2006-06-17 |  57  |
  | 2006-06-29 |  74  |
  | 2006-06-29 |  94  |
  | 2006-06-30 |  26  |
  +------------+------+

If you want to see the data for the preceding month do not changeMONTH(whn)=MONTH(CURRENT_DATE)toMONTH(whn)=MONTH(CURRENT_DATE)-1. If you do that you will get data from the wrong year when you run this query in January. Instead, you need to subtract one month fromCURRENT_DATE. It is a little neater if you do the date calculation in a nestedSELECT:

  mysql> SELECT * FROM t,
    
->  
(SELECT CURRENT_DATE - INTERVAL 1 MONTH lastMnth) p
    
->  WHERE MONTH(whn)=MONTH(lastMnth)
    
->    AND YEAR(whn)=YEAR(lastMnth);
  +------------+------+------------+
  | whn        | v    | lastMnth   |
  +------------+------+------------+
  | 2006-05-04 |  43  | 2006-05-23 |
  | 2006-05-06 |  55  | 2006-05-23 |
  | 2006-05-08 |  89  | 2006-05-23 |
  | 2006-05-15 |  87  | 2006-05-23 |
  | 2006-05-22 |  90  | 2006-05-23 |
  | 2006-05-29 |  22  | 2006-05-23 |
  | 2006-05-30 |  27  | 2006-05-23 |
  +------------+------+------------+
 

SQL Server.In SQL Server, you should use theDATEADDfunction. You can specify the interval asmfor month and the number of months as-1:

  SELECT * FROM t,
    (SELECT DATEADD(m,-1,GETDATE()) lastMnth) p
    WHERE MONTH(whn)=MONTH(lastMnth)
      AND YEAR(whn)=YEAR(lastMnth);

Oracle.You need to use thedualtable in a subselect in Oracle. Also, you can use theTO_CHARfunction to match the year and month in a single function:

  SELECT * FROM t,
    (SELECT CURRENT_DATE - INTERVAL '1' MONTH lastMnth FROM dual)
    WHERE TO_CHAR(whn,'yyyymm')=TO_CHAR(lastMnth,'yyyymm');


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