Home arrow SQL arrow Page 2 - Generating Reports with SQL Date Handling
SQL

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:
By: O'Reilly Media
Rating: 4 stars4 stars4 stars4 stars4 stars / 15
December 27, 2007
TABLE OF CONTENTS:
  1. · Generating Reports with SQL Date Handling
  2. · Hacking the Hack
  3. · HACK 23 Second Tuesday of the Month
  4. · The Formula
  5. · Hacking the Hack: The Last Thursday of the Month

print this article
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

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


blog comments powered by Disqus
SQL ARTICLES

- Focusing SQL Queries
- Complex SQL Queries
- A Close Look at the SQL Query
- Generating Reports with SQL Date Handling
- Creating SQL Reports Based on Date Criteria
- SQL Date Handling and Data Trends
- Date Handling
- Introduction to SQL
- Lies, Damn Lies, Statistics, and SQL

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials