Generating Reports with SQL Date Handling
(Page 1 of 5 )
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.
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 | | |
Next: Hacking the Hack >>
More SQL Articles
More By O'Reilly Media
|
 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.
|
|
