Generating Reports with SQL Date Handling - Hacking the Hack

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).

Next: HACK 23 Second Tuesday of the Month >>
 

More SQL Articles
More By O'Reilly Media

Comments On: Generating Reports with SQL Date Handling

· This article is an excerpt from the book "SQL Hacks," published by O'Reilly. We hope...   >>> Post your comment now!

Buy this book...

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.