Generating Reports with SQL Date Handling

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