PLSQL-estimate specific hours of business days between two dates

I have a query that 开发者_Python百科uses a difference between two dates in order to find the duration used somewhere else in the query' Firstly, i want to exclude no business days, and secondly, from the remaining ones to use only a predefined working hour period (i.e 08:00 - 20:00). This working hour period will be the same for Monday-Wednesday-Friday (08:00-20:00) and 09:00-17:00 for Tuesday and Thursday.

If you have a table of days and working hours like this:

select * from days;

day hours
1   0
2   12
3   8
...

then this query will do it:

select sum( ( (next_day(:end_date-7,days.day)
               -next_day(:start_date-1,days.day)
              ) / 7 + 1
            ) * days.hours
          ) num
from days

There is a Formula which can be implemented in any language:

number_of_days - math_round_down(10 * (number_of_days / (business_days_in_a_week * days_in_a_week)))

see more : "How to find business days in current month with Javascript?"