Home arrow SQL arrow Generating Reports with SQL Date Handling
SQL

Generating Reports with SQL Date Handling


In this conclusion to a four-part series on SQL date handling, you will learn how to generate quarterly reports -- and how to hack the technique to generate reports at other times covering other periods of time. 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 / 15
December 27, 2007
TABLE OF CONTENTS:
  1. · Generating Reports with SQL Date Handling
  2. · Hacking the Hack
  3. · HACK 23 Second Tuesday of the Month
  4. · The Formula
  5. · Hacking the Hack: The Last Thursday of the Month

print this article
SEARCH DEVARTICLES

Generating Reports with SQL Date Handling
(Page 1 of 5 )

HACK 22 Generate Quarterly Reports

A quarterly report aggregates three months’ worth of figures. SQL has all the functions you need to get this aggregation.

Suppose you have figures that you need to report on by quarter. The source of your data is just a list of dates and values, as shown in Table 4-4.

Table 4-4. The sale table

whn

amount

2005-01-06

2

2005-03-14

8

2005-04-02

4

In a quarterly report, you need toSUMall the figures relating to January, February, and March into Q1. So the first two rows of Table 4-4 contribute to the Q1 total for 2005. The 2005-04-02 row occurred in April, so you should add it to the Q2 total for 2005.

You can use theMONTHfunction to extract the month as a number, with January, February, and March appearing as 1, 2, and 3.

If you also group on the year, each quarter of your input will correspond to exactly one cell in the output grid:

  mysql> SELECT YEAR(whn) AS yr
     ->    ,SUM(CASE WHEN MONTH(whn) IN (1,2,3)         THEN amount END) AS Q1
     ->    ,SUM(CASE WHEN MONTH(whn) IN (4,5,6)         THEN amount END) AS Q2
     ->    ,SUM(CASE WHEN MONTH(whn) IN (7,8,9)         THEN amount END) AS Q3
     ->    ,SUM(CASE WHEN MONTH(whn) IN (10,11,12)      THEN amount END) AS Q4
     -> FROM sale
   
-> GROUP BY YEAR(whn);

  +------+------+------+------+------+
  | yr   | Q1   | Q2   | Q3   |  Q4  |
  +------+------+------+------+------+
  | 2005 |  10  |  40  |  80  |  660 |
  | 2006 |  30  |  20  | NULL | NULL |
  +------+------+------+------+------+

Unfortunately, theYEAR andMONTHfunctions are not implemented in Oracle. However, the SQL standardEXTRACTfunction works just as well:

  SQL> SELECT EXTRACT(YEAR FROM whn) AS yr
    2     ,SUM(CASE WHEN EXTRACT(MONTH FROM whn) IN (1,2,3)
    3        THEN amount END) AS Q1
    4     ,SUM(CASE WHEN EXTRACT(MONTH FROM whn) IN (4,5,6)
    5        THEN amount END) AS Q2
    6     ,SUM(CASE WHEN EXTRACT(MONTH FROM whn) IN (7,8,9)
    7        THEN amount END) AS Q3
    8     ,SUM(CASE WHEN EXTRACT(MONTH FROM whn) IN (10,11,12)
    9        THEN amount END) AS Q4
   10   FROM sale
   11  GROUP BY EXTRACT(YEAR FROM whn);
  

 

YR

Q1

Q2

Q3

Q4

2005

10

40

80

660

2006

30

20

 

 


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