Question: I have 2 tables: the 1st one contains the start date and the end date of a purchase order,
and the 2nd table contains year hollidays

-purchase order

![enter image description here](https://i.stack.imgur.com/fO8I8.gif)

-Holidays

![](https://i.stack.imgur.com/IXwE5.gif)

I'm tryign to calculate the number of business days between 2 dates without the weekends and the holidays.

the output should be like this:

```
Start Date | End Date | Business Days

```

Could you please help me

Answer: 
You can remove the non-weekend holidays with a query like this:

```
select (t.end_date - t.start_date) - count(c.date)
from table1 t left join
     calendar c
     on c.date between t1.start_date and t1.end_date and
        to_char(c.date, 'D') not in ('1', '7')
group by t.end_date, t.start_date;

```

Removing the weekend days is then more complication. Full weeks have two weekend days, so that is easy. So a good approximation is:

```
select (t.end_date - t.start_date) - (count(c.date) +
       2 * floor((t.end_date - t.start_date) / 7))
from table1 t left join
     calendar c
     on c.date between t1.start_date and t1.end_date and
        to_char(c.date, 'D') not in ('1', '7')
group by t.end_date, t.start_date;

```

This doesn't get the day of week, which is essentially if the end date is before the start date, then it is in the following week. However, this logic gets rather complicated the way that Oracle handles day of the week, so perhaps the above approximation is sufficient.
This query should produce exact number of business days for each range in `purchase` table:

```
with days as (
  select rn, sd + level - 1 dt, sd, ed 
    from (select row_number() over (order by start_date) rn, 
                 start_date sd, end_date ed from purchase_order)
    connect by prior rn = rn and sd + level - 1 <= ed
           and prior dbms_random.value is not null)
select sd start_date, ed end_date, count(1) business_days
  from days d left join holidays h on holiday_date = d.dt
  where dt - trunc(dt, 'iw') not in (5, 6) and h.holiday_date is null
  group by rn, sd, ed

```

[SQLFiddle demo](http://sqlfiddle.com/#!4/1e608/1)

For each row in `purchase_orders` query generates dates from this range (this is done by subquery `dates`). 
Main query checks if this is weekend day or holiday day and counts rest of dates.

Hierarchical query used to generate dates may cause slowdowns if there is big number of data in `purchase_orders` 
or periods are long. In this case preferred way is to create calendar table, as already suggested in comments.