Home arrow SQL arrow Page 4 - 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 - The Formula
(Page 4 of 5 )

Converting the first day of the month into the second Tuesday of the month simply involves manipulating the day of the week of the first day with an arithmetic formula. Before you see the formula, you should review what happens when the first day of the month falls on each day of the week, from Sunday through Saturday.

If the first day of the month is:

  1. A Sunday, the third is a Tuesday, so the tenth is the second Tuesday.

  2. A Monday, the second is a Tuesday, so the ninth is the second Tuesday.

  3. A Tuesday, the eighth is the second Tuesday.

  4. A Wednesday, the seventh is the next Tuesday, so the fourteenth is the second Tuesday.

  5. A Thursday, the sixth is the next Tuesday, so the thirteenth is the second Tuesday.

  6. A Friday, the fifth is the next Tuesday, so the twelfth is the second Tuesday.

  7. A Saturday, the fourth is the next Tuesday, so the eleventh is the second Tuesday.

This exhausts all possibilities. So the challenge now is simply to reduce these facts into a formula. With the aid of an underappreciated technological methodology called brute force, you can verify the correctness of the following manipulation of the day of the week of the first day of the month, as shown in Table 4-6.

Table 4-6. Demonstration calculation

A 1st

B wkday

C 10B

D C mod 7

E D+7

sun

1

9

2

9

mon

2

8

1

8

tue

3

7

0

7

wed

4

6

6

13

thu

5

5

5

12

fri

6

4

4

11

sat

7

3

3

10

The first column (A) is the day of the week of the first day of the month, and the second column is the numerical equivalent of this, using the range 1 (Sunday) through 7 (Saturday).

The important data in Table 4-6 is in the last column, which is the number of days to add to the date of the first day of the month.

So in a nutshell, the formula is:

  1. Find B, the day of the week of the first day of the month, using:

      1=Sunday ... 7=Saturday.

  2. Subtract this number from 10 to get C: 

       With Sunday=1 Saturday=7, Tuesday would be 3.

    The number 3 B is the offset (relative to the first of the month) for a Tuesday, but it might be in the current month or the previous month; 10 B is also a Tuesday, and so are 17 B and 24 B.

    You should choose to subtract from 10 because you want C to be positive for all inputs. This is because you need D to be positive in the next step, but a negative value for C would result in a negative value for D. This is because 1%7 gives 1 on most systems.

  3. Divide by 7 and keep the remainder to get D.

    D is also the offset for a Tuesday, and D is in the range 0 to 6. Every day in the first week has an offset between 0 and 6. So D is the first Tuesday of the month.

  4. Add 7 to get E.

    That takes the range of E from 7 to 13. Every day in the second week has an offset in the range 713.

  5. Take the result and add that number of days to the date of the first day of the month.

In practical terms, to implement this formula you will need to use the specific date and arithmetic functions of your database system. Here are some examples.

MySQL.  In MySQL:

  SELECT monthdate   AS first_day_of_month
      , DATE_ADD(monthdate
               
, INTERVAL
                  ( ( 10 - DAYOFWEEK(monthdate) ) % 7 ) + 7
                  DAY
               
)     AS second_tuesday_of_month
    FROM monthdates

Oracle.  In Oracle:

  SELECT monthdate   AS first_day_of_month
       , monthdate
          
+ MOD( ( 10 TO_CHAR(monthdate,'d') ), 7 ) + 7
                   
AS second_tuesday_of_month
   FROM monthdates

SQL Server.With SQL Server:

  SELECT monthdate   AS first_day_of_month
      
, DATEADD(day
                , ( ( 10 - DATEPART(dw,monthdate) ) % 7 ) + 7
                , monthdate
                )     AS second_tuesday_of_month
  
FROM monthdates

PostgreSQL. PostgreSQL gives 0 for Sunday, so you must add 1. Also, the output fromEXTRACTis a floating-point number, so you mustCASTit before you attempt modular arithmetic:

  SELECT monthdate   AS first_day_of_month
       , monthdate +
            ((10 - CAST(EXTRACT(dow FROM monthdate) + 1 AS INT)) % 7) + 7
                     AS second_tuesday_of_month
   FROM monthdates

Results:  

 

 

first_day_of_month

second_tuesday_of_month

2007-04-01

2007-04-10

2007-05-01

2007-05-08

2007-06-01

2007-06-12


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