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?"
精彩评论