Recipe8.3.Determining the Number of Business Days Between Two Dates

Recipe 8.3. Determining the Number of Business Days Between Two Dates

Problem

Given two dates, you want to find how many "working" days are between them, including the two dates themselves. For example, if January 10th is a Tuesday and January 11th is a Monday, then the number of working days between these two dates is two, as both days are typical work days. For this recipe, "business days" is defined as any day that is not Saturday or Sunday.

Solution

The solution examples find the number of business days between the HIREDATEs of BLAKE and JONES. To determine the number of business days between two dates, you can use a pivot table to return a row for each day between the two dates (including the start and end dates). Having done that, finding the number of business days is simply counting the dates returned that are not Saturday or Sunday.

If you want to exclude holidays as well, you can create a HOLIDAYS table. Then include a simple NOT IN predicate to exclude days listed in HOLIDAYS from the solution.

DB2

Use the pivot table T500 to generate the required number of rows (representing days) between the two dates. Then count each day that is not a weekend. Use the DAYNAME function to return the weekday name of each date. For example:

  1 select sum(case when dayname(jones_hd+t500.id day -1 day)  2                   in ( 'Saturday','Sunday' )  3                 then 0 else 1  4            end) as days  5   from (  6 select max(case when ename = 'BLAKE'  7                 then hiredate  8            end) as blake_hd,  9        max(case when ename = 'JONES' 10                 then hiredate 11            end) as jones_hd 12   from emp 13  where ename in ( 'BLAKE','JONES' ) 14        ) x, 15        t500 16  where t500.id <= blake_hd-jones_hd+1

MySQL

Use the pivot table T500 to generate the required number of rows (days) between the two dates. Then count each day that is not a weekend. Use the DATE_ADD function to add days to each date. Use the DATE_FORMAT function to obtain the weekday name of each date:

  1 select sum(case when date_format(  2                         date_add(jones_hd,  3                                  interval t500.id-1 DAY),'%a')  4                   in ( 'Sat','Sun' )  5                 then 0 else 1  6            end) as days  7   from (  8 select max(case when ename = 'BLAKE'  9                 then hiredate 10            end) as blake_hd, 11        max(case when ename = 'JONES' 12                 then hiredate 13             end) as jones_hd 14   from emp 15  where ename in ( 'BLAKE','JONES' ) 16        ) x, 17        t500 18  where t500.id <= datediff(blake_hd,jones_hd)+1

Oracle

Use the pivot table T500 to generate the required number of rows (days) between the two dates, and then count each day that is not a weekend. Use the TO_CHAR function to obtain the weekday name of each date:

  1 select sum(case when to_char(jones_hd+t500.id-1,'DY')  2                   in ( 'SAT','SUN' )  3                 then 0 else 1  4            end) as days  5   from (  6 select max(case when ename = 'BLAKE'  7                 then hiredate  8            end) as blake_hd,  9        max(case when ename = 'JONES' 10                 then hiredate 11            end) as jones_hd 12   from emp 13  where ename in ( 'BLAKE','JONES' ) 14        ) x, 15        t500 16  where t500.id <= blake_hd-jones_hd+1

PostgreSQL

Use the pivot table T500 to generate the required number of rows (days) between the two dates. Then count each day that is not a weekend. Use the TO_CHAR function to obtain the weekday name of each date:

  1 select sum(case when trim(to_char(jones_hd+t500.id-1,'DAY'))  2                   in ( 'SATURDAY','SUNDAY' )  3                 then 0 else 1  4            end) as days  5   from (  6 select max(case when ename = 'BLAKE'  7                 then hiredate  8            end) as blake_hd,  9        max(case when ename = 'JONES' 10                 then hiredate 11            end) as jones_hd 12   from emp 13  where ename in ( 'BLAKE','JONES' ) 14        ) x, 15        t500 16  where t500.id <= blake_hd-jones_hd+1

SQL Server

Use the pivot table T500 to generate the required number of rows (days) between the two dates, and then count each day that is not a weekend. Use the DATENAME function to obtain the weekday name of each date:

  1 select sum(case when datename(dw,jones_hd+t500.id-1)  2                   in ( 'SATURDAY','SUNDAY' )  3                  then 0 else 1  4            end) as days  5   from (  6 select max(case when ename = 'BLAKE'  7                 then hiredate  8            end) as blake_hd,  9        max(case when ename = 'JONES' 10                 then hiredate 11            end) as jones_hd 12   from emp 13  where ename in ( 'BLAKE','JONES' ) 14        ) x, 15        t500 16  where t500.id <= datediff(day,jones_hd-blake_hd)+1

Discussion

While each RDBMS requires the use of different built-in functions to determine the name of a day, the overall solution approach is the same for each. The solution can be broken into two steps:

Return the days between the start date and end date (inclusive).
Count how many days (i.e., rows) there are, excluding weekends.

Inline view X performs step 1. If you examine inline view X, you'll notice the use of the aggregate function MAX, which the recipe uses to remove NULLs. If the use of MAX is unclear, the following output might help you understand. The output shows the results from inline view X without MAX:

  select case when ename = 'BLAKE'             then hiredate        end as blake_hd,        case when ename = 'JONES'             then hiredate        end as jones_hd   from emp  where ename in ( 'BLAKE','JONES' ) BLAKE_HD    JONES_HD ----------- -----------             02-APR-1981 01-MAY-1981

Without MAX, two rows are returned. By using MAX you return only one row instead of two, and the NULLs are eliminated:

  select max(case when ename = 'BLAKE'             then hiredate        end) as blake_hd,        max(case when ename = 'JONES'             then hiredate        end) as jones_hd   from emp  where ename in ( 'BLAKE','JONES' ) BLAKE_HD    JONES_HD ----------- ----------- 01-MAY-1981 02-APR-1981

The number of days (inclusive) between the two dates here is 30. Now that the two dates are in one row, the next step is to generate one row for each of those 30 days. To return the 30 days (rows), use table T500. Since each value for ID in table T500 is simply 1 greater than the one before it, add each row returned by T500 to the earlier of the two dates (JONES_HD) to generate consecutive days starting from JONES_HD up to and including BLAKE_HD. The result of this addition is shown below (using Oracle syntax):

  select x.*, t500.*, jones_hd+t500.id-1   from ( select max(case when ename = 'BLAKE'                 then hiredate            end) as blake_hd,        max(case when ename = 'JONES'                 then hiredate            end) as jones_hd   from emp  where ename in ( 'BLAKE','JONES' )        ) x,        t500  where t500.id <= blake_hd-jones_hd+1 BLAKE_HD    JONES_HD            ID JONES_HD+T5 ----------- ----------- ---------- ----------- 01-MAY-1981 02-APR-1981          1 02-APR-1981 01-MAY-1981 02-APR-1981          2 03-APR-1981 01-MAY-1981 02-APR-1981          3 04-APR-1981 01-MAY-1981 02-APR-1981          4 05-APR-1981 01-MAY-1981 02-APR-1981          5 06-APR-1981 01-MAY-1981 02-APR-1981          6 07-APR-1981 01-MAY-1981 02-APR-1981          7 08-APR-1981 01-MAY-1981 02-APR-1981          8 09-APR-1981 01-MAY-1981 02-APR-1981          9 10-APR-1981 01-MAY-1981 02-APR-1981         10 11-APR-1981 01-MAY-1981 02-APR-1981         11 12-APR-1981 01-MAY-1981 02-APR-1981         12 13-APR-1981 01-MAY-1981 02-APR-1981         13 14-APR-1981 01-MAY-1981 02-APR-1981         14 15-APR-1981 01-MAY-1981 02-APR-1981         15 16-APR-1981 01-MAY-1981 02-APR-1981         16 17-APR-1981 01-MAY-1981 02-APR-1981         17 18-APR-1981 01-MAY-1981 02-APR-1981         18 19-APR-1981 01-MAY-1981 02-APR-1981         19 20-APR-1981 01-MAY-1981 02-APR-1981         20 21-APR-1981 01-MAY-1981 02-APR-1981         21 22-APR-1981 01-MAY-1981 02-APR-1981         22 23-APR-1981 01-MAY-1981 02-APR-1981         23 24-APR-1981 01-MAY-1981 02-APR-1981         24 25-APR-1981 01-MAY-1981 02-APR-1981         25 26-APR-1981 01-MAY-1981 02-APR-1981         26 27-APR-1981 01-MAY-1981 02-APR-1981         27 28-APR-1981 01-MAY-1981 02-APR-1981         28 29-APR-1981 01-MAY-1981 02-APR-1981         29 30-APR-1981 01-MAY-1981 02-APR-1981         30 01-MAY-1981

If you examine the WHERE clause, you'll notice that you add 1 to the difference between BLAKE_HD and JONES_HD to generate the required 30 rows (otherwise, you would get 29 rows). You'll also notice that you subtract 1 from T500.ID in the SELECT list of the outer query, since the values for ID start at 1 and adding 1 to JONES_HD would cause JONES_HD to be excluded from the final count.

Once you generate the number of rows required for the result set, use a CASE expression to "flag" whether or not each of the days returned are weekdays or weekends (return a 1 for a weekday and a 0 for a weekend). The final step is to use the aggregate function SUM to tally up the number of 1s to get the final answer.