Calculating Business Days and Business Days Between

From the Database Geek.

I recently had a requirement to populate the day dimension of a data mart (I won’t put all of the code here as it’s pretty large). That’s not that big deal but part of the requirement was to set several columns: BUSINESS_DAY_FLAG, BUSINESS_DAY_NO and BUSINESS_DAYS_REMAINING_NO.

• The BUSINESS_DAY_FLAG is Y is the date is MON-FRI and N is the date is SAT or SUN.
• BUSINESS_DAY_NO is the business day of the month. There are 5 business days per week so if the month started on a monday, the second monday would be business day 6.
• BUSINESS_DAYS_REMAINING_NO is the number of business days remaining in the month. There are either 20, 21, or 22 business days in a month and those will be spread across 28, 29, 30 or 31 days.

No holidays are considered in this. For our uses, that is not needed. Weekdays and business days are really interchangeable here.

The Tasks

Now that I have my requirement, how do I solve the problem? I do it the same way that I solve any problem, I break the problem into it’s component tasks. In no particular order, I see the following tasks:

• write an insert statement to insert into DIM_DAY (not included here)
• determine the number of business days in a month
• determine the current business day number
• determine how to handle a business day number on the weekend
• set a flag for business day

Defining a Base Calendar

First up is to set a flag for a business day and determine how to handle the day number on the weekend. After discussing with the user, it is determined that: if I ask for a date that has a BUSINESS_DAY_FLAG of N then the BUSINESS_DAY_NO will be the same BUSINESS_DAY_NO as the last business day (that would be the previous Friday).

Cool, let’s figure out what a business day is. I need a base set of records to work with. You can use CONNECT BY to generate records (and if you are doing millions of records, that may be your best choice). I prefer to just use ALL_OBJECTS. For this data mart, we will only insert a 2 years at a time so ALL_OBJECTS has plenty enough records.

So now I use ALL_OBJECTS to generate a range of dates. To generate a range, we set a starting date and select that against ALL_OBJECTS for a certain number of rows (in my case I will just say 1000 to keep it simple):

SELECT   TO_DATE ('31-dec-2008', 'DD-MON-YYYY') + rownum the_date
  FROM   all_objects
  WHERE  ROWNUM < 1000

This is a very simple query and I will use it as the basis for all of the computations that I have yet to make. I picked 31-DEC-2008 because it fits my needs. You can start with any date that works for you.

Notice that I add rownum to the date to advance though the dates. You could put your start date in a table and make this very dynamic. Put it in a global temporary table and you could even make it dynamic per session and compute things like this in real time.

Calculate Business Days

Now, I add the query that will determine if a day is a business day. I’m not quite ready to set the flag yet. I’ll need a number to count the days within a month. For that I will just set a 1 for business day and a 0 for weekend.

SELECT   CASE TO_NUMBER (TO_CHAR (the_date, 'D'))
           WHEN 1 THEN 0
           WHEN 7 THEN 0
           ELSE 1
         END business_day,
         the_date
  FROM  ( [days base query] )

This query gives me a date and a 1 or 0 depending on the day of week.

BUSINESS_DAY           THE_DATE
---------------------- -------------------------
1                      01-JAN-09
1                      02-JAN-09
0                      03-JAN-09
0                      04-JAN-09
1                      05-JAN-09

Are we having fun yet? This query could be wrapped in a view and used to calculate business days very easily. If you were to do that, I would suggest that you make the start date and the number of rows be dynamic (either through a table or with binds). Example: If we create a view:

CREATE OR REPLACE VIEW business_days
AS
SELECT   CASE TO_NUMBER (TO_CHAR (the_date, 'D'))
           WHEN 1 THEN 0
           WHEN 7 THEN 0
           ELSE 1
         END business_day,
         the_date
            FROM   (
              SELECT   TO_DATE ('31-dec-2008', 'DD-MON-YYYY') + rownum the_date
                FROM   all_objects
                WHERE  ROWNUM < 1000 )
/

And then select from that view:

SELECT SUM (business_day)
  FROM business_days
  WHERE the_date BETWEEN TO_DATE ('31-dec-2008', 'DD-MON-YYYY')
                     AND TO_DATE ('31-dec-2010', 'DD-MON-YYYY')

SUM(BUSINESS_DAY)
-----------------
              522

You could very easily turn this into a function so that you could call it just like TO_DATE or LAST_DAY.

Business Days in a Month

That’s a good start but I still need to figure out the current business day of the month and the number remaining in the month. This next query uses the same view and will tell me the current week day (i.e.. business day) of the month as well as the total number of weekdays in the month.

SELECT the_date,
       sum(business_day) over
              (partition by to_char(the_date, 'MON-YYYY'))
           business_days_in_month,
       sum(business_day) over
              (partition by to_char(the_date, 'MON-YYYY')
                 order by to_char(the_date, 'DD-MON-YYYY')
                 rows to_char(the_date, 'DD') preceding)
           weekday_of_month_no,
           decode( business_day, 1, 'Y', 0, 'N') week_day_flag
  FROM business_days
  WHERE the_date BETWEEN TO_DATE ('31-dec-2008', 'DD-MON-YYYY')
                     AND TO_DATE ('31-dec-2010', 'DD-MON-YYYY')
ORDER BY the_date

The results of a select to this query looks like this:

THE_DATE  BUSINESS_DAYS_IN_MONTH WEEKDAY_OF_MONTH_NO W
--------- ---------------------- ------------------- -
01-JAN-09                     22                   1 Y
02-JAN-09                     22                   2 Y
03-JAN-09                     22                   2 N
04-JAN-09                     22                   2 N
05-JAN-09                     22                   3 Y
06-JAN-09                     22                   4 Y
07-JAN-09                     22                   5 Y

The Final Select

I could have finished up with the query by subtracting the current week day number from the total but that would have required me to duplicate some of the logic for calculating the current and totals day so I prefer to wrap that in a parent query. Here is the final query that calculates all of the fields to meet the user requirements. I am using the sub-selects instead of the view:

SELECT the_date,
       business_days_in_month,
       weekday_of_month_no,
       week_day_flag,
       business_days_in_month - weekday_of_month_no week_days_remaining
FROM (
  SELECT the_date,
       sum(business_day) over
              (partition by to_char(the_date, 'MON-YYYY'))
           business_days_in_month,
       sum(business_day) over
              (partition by to_char(the_date, 'MON-YYYY')
                 order by to_char(the_date, 'DD-MON-YYYY')
                 rows to_char(the_date, 'DD') preceding)
           weekday_of_month_no,
           decode( business_day, 1, 'Y', 0, 'N') week_day_flag
    FROM (
     SELECT   CASE TO_NUMBER (TO_CHAR (the_date, 'D'))
                WHEN 1 THEN 0
                WHEN 7 THEN 0
                ELSE 1
              END business_day,
              the_date
       FROM   (
         SELECT   TO_DATE ('31-dec-2008', 'DD-MON-YYYY') + rownum the_date
           FROM   all_objects
           WHERE  ROWNUM < 1000 )
  )
)
WHERE the_date BETWEEN TO_DATE ('31-dec-2008', 'DD-MON-YYYY')
                     AND TO_DATE ('31-dec-2010', 'DD-MON-YYYY')
ORDER BY the_date

And the final output:

THE_DATE  BUSINESS_DAYS_IN_MONTH WEEKDAY_OF_MONTH_NO W WEEK_DAYS_REMAINING
--------- ---------------------- ------------------- - -------------------
01-JAN-09                     22                   1 Y                  21
02-JAN-09                     22                   2 Y                  20
03-JAN-09                     22                   2 N                  20
04-JAN-09                     22                   2 N                  20
05-JAN-09                     22                   3 Y                  19
06-JAN-09                     22                   4 Y                  18
07-JAN-09                     22                   5 Y                  17

There it is. Is this the only way to do it? Nope. I had fun doing it though.

NOTE: Use this at your own risk. I tested it and it seems to work for what I need to use it for. Your mileage may vary. Before using it in production, I strongly suggest that YOU test it in YOUR environment.

On that note, if you do find any bugs, please let me know. I will update the code (here and in my system).

LewisC

Technorati : business, calendar, data mart, dim day, dimension, pl sql, sql