difference between two dates without weekends and holidays Sql query ORACLE

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

-Holidays

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 1

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.