CODEWITHSUNDEEP
sqloracle-database
Negashion
Negashion

Reputation: 139

Counting business days between two dates for each row in a table

I'm trying to implement this askTom solution to count the business days between two date fields in a table.

 select count(*)
   from ( select rownum rnum
            from all_objects
           where rownum <= to_date('&1') - to_date('&2')+1 )
  where to_char( to_date('&2')+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' )

I don't know how I can pass values to toms code or how to do a work around so that each time the select executes with a different set of dates and that way obtaining a similar output :

rowid | bussiness_days

I guess this could be implemented with a PL/SQL block but I'd prefer to keep it down to a query if possible. Could it be possible to pass values using &1 parameters from a select above toms one?

Upvotes: 1

Views: 345

Answers (3)

A.B.Cade
A.B.Cade

Reputation: 16915

This is not like the original askTom, but if you're using 11gR2 you can use a Recursive CTE:

with rcte(a,b, i, is_wd) as (
select from_dt , to_dt , id, case when (to_char(from_dt, 'DY') in ('SAT','SUN')) then 0 else 1 end from t
  union all
  select decode(a, null, a,a+1), b, i, case when (to_char(a, 'DY') in ('SAT','SUN')) then 0 else 1 end 
  from rcte
  where a+1 <= b
)
select i id, sum(is_wd) 
from rcte
group by i

where t is a table containing "from_dates" and "to_dates"

Here is a sqlfiddle demo

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59642

Try this one:

SELECT COUNT(*)
   FROM ( SELECT ROWNUM rnum
            FROM all_objects
           WHERE ROWNUM <= TO_DATE('2014-02-07','yyyy-mm-dd') - TO_DATE('2014-02-01','yyyy-mm-dd')+1 )
  WHERE TO_CHAR( TO_DATE('2014-02-01','yyyy-mm-dd')+rnum-1, 'DY' ) NOT IN ( 'SAT', 'SUN' )

However, it has at least two issues:

  • It presumes the session NLS_DATE_LANGUAGE is set to English
  • The query gives wrong result if number of rows in ALL_OBJECTS is smaller than the amount of days between your ranges.

The version is less error-prone and faster:

WITH t AS   
    (SELECT TO_DATE('2014-02-01','yyyy-mm-dd')+LEVEL-1 the_date 
    FROM dual
    CONNECT BY TO_DATE('2014-02-01','yyyy-mm-dd')+LEVEL-1 <= TO_DATE('2014-02-07','yyyy-mm-dd'))
SELECT COUNT(*)
FROM t
WHERE TO_CHAR(the_date, 'DY', 'NLS_DATE_LANGUAGE = AMERICAN') NOT IN ( 'SAT', 'SUN' )

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

This is another situation where a calendar table comes in handy. A calendar table has a row for every date.

If Saturday and Sunday are not business days, it's quite likely that holidays are not either. A field in the calendar table to indicate holidays makes it a lot easier to exclude them from your business days calculation.

Upvotes: 0

Related Questions