Home SQL Page 2 - Generating Reports with SQL Date Handling

# 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:
Rating:  / 15
December 27, 2007

SEARCH DEVARTICLES

Generating Reports with SQL Date Handling - Hacking the Hack
(Page 2 of 5 )

You may want to pivot the rows and columns of the report. In standard SQL, you have to apply some math:

mysql> SELECT FLOOR((EXTRACT(MONTH FROM whn)-1)/3)+1 AS Quarter
->    ,SUM(CASE WHEN EXTRACT(YEAR,whn)=2005 THEN amount END) AS Y2005

->    ,SUM(CASE WHEN EXTRACT(YEAR,whn)=2006 THEN amount END) AS Y2006
-> FROM sale
-> GROUP BY FLOOR((EXTRACT(MONTH FROM whn)-1)/3)+1;

+---------+-------+-------+
| Quarter | Y2005 | Y2006 |
+---------+-------+-------+
|       1 |    10 |    30 |
|       2 |    40 |    20 |
|       3 |    80 |  NULL |
|       4 |   660 |  NULL |
+---------+-------+-------+

The expressionFLOOR((MONTH(whn)-1)/3)+1calculates the quarter for the input datewhn. You can see how it works if you look at the calculation one step at a time:

mysql> SELECT whn, EXTRACT(MONTH FROM whn)                     'Month',
->              EXTRACT(MONTH FROM whn)-1                   'Subtract 1',
->               (EXTRACT(MONTH FROM whn)-1)/3           'Divide by 3',
->               FLOOR((EXTRACT(MONTH FROM whn)-1)/3)   'Ignore Fraction',
->               FLOOR((EXTRACT(MONTH FROM whn)-1)/3)+1 'Add 1'

-> FROM sale
-> WHERE YEAR(whn)=2006;

 whn Month  Subtract 1 Divide by 3 Ignore Fraction Add 1 2006-01-01 1 0 0.0000 0 1 2006-02-01 2 1 0.3333 0 1 2006-03-01 3 2 0.6667 0 1 2006-04-01 4 3 1.0000 1 2 2006-05-01 5 4 1.3333 1 2

Each vendor has a function to extract theQUARTERand theYEAR from a date. In MySQL, these functions areQUARTERandYEAR:

mysql> SELECT QUARTER(whn)

->   ,SUM(CASE WHEN YEAR(whn)=2005 THEN amount END) AS Y2005

->   ,SUM(CASE WHEN YEAR(whn)=2006 THEN amount END) AS Y2006
-> FROM sale
-> GROUP BY QUARTER(whn);

+--------------+-------+-------+
| QUARTER(whn) | Y2005 | Y2006 |
+--------------+-------+-------+
|            1 |    10 |    30 |
|            2 |    40 |    20 |
|            3 |    80 |  NULL |
|            4 |   660 |  NULL |
+--------------+-------+-------+

The trick is toGROUP BYthe quarter and use theCASEstatement to extract only one year in each column. There are some database-specific variations to keep in mind:

• MySQL usesQUARTER(whn)andYEAR(whn)as shown.
• In SQL Server, you can useDATEPART(QUARTER,whn) andYEAR(whn).
• In Oracle, you can useTO_CHAR(whn,'Q')andTO_CHAR(whn,'YYYY')for the quarter and year.
1. In PostgreSQL, you can useEXTRACT(QUARTER FROM whn)andEXTRACT(YEAR FROM whn).
2. In Access, you can useDatePart("q", whn)andYEAR(whn).