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);
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);
SQL 
/ 8 
SQL Date Handling and Data Trends
