sinner052397
asked on
Working with dates
Hello,
I have to figure out how many workdays are within a specified range.
I know that through dual I can get the TOTAL number of days between the dates but how do I eliminate the weekends?
If possible, I would like to avoid a loop where it checks one day at a time because the ranges might be up to 12 years.
Thanks for your help.
I have to figure out how many workdays are within a specified range.
I know that through dual I can get the TOTAL number of days between the dates but how do I eliminate the weekends?
If possible, I would like to avoid a loop where it checks one day at a time because the ranges might be up to 12 years.
Thanks for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sinner, to quote yourself
"If possible, I would like to avoid a loop where it checks one day at a time because the ranges might be up to 12 years."
Not only does the answer you accepted do exactly what you did not want to do, it cannot handle holidays etc, I suggest that if you want an accurate answer you will have to come up with a more sophistcated solution. I also suggest that, if you want a solution to your problem that meets your needs, you give people a chance to come up with a solution that meets your needs. Of course if the proposed answer meets your needs than why did you bother asking for a solution that didn't loop through the dates? to me it looks like you have accepted an answer which you already knew, or were you just looking for someone to do the coding for you?.
As an alternative you could adopt my suggestion and modify it a bit.
1) find out what day of the week the start and end dates fall on.
2) from the start date loop forward until the next instance of the day the end date falls on (eg if the start day is a wednesday and the end date a monday, count from the start date until the next monday) and count the number of working days in between (using a test as in the junfeb answer). Take this new instance of the day your new start date
3) count the number of days between the new start date and the end date, multiply it by 5/7 to give the number of working days between.
4) add the two counts together to give the total number of days between the dates.
I suspect that if your dates are 12 years apart this might be faster than looping through all the days, you could also bundle it into a function and select it using your SQL statement e.g. SELECT days_between(start_date, end_date) from dual;
Vlad.
"If possible, I would like to avoid a loop where it checks one day at a time because the ranges might be up to 12 years."
Not only does the answer you accepted do exactly what you did not want to do, it cannot handle holidays etc, I suggest that if you want an accurate answer you will have to come up with a more sophistcated solution. I also suggest that, if you want a solution to your problem that meets your needs, you give people a chance to come up with a solution that meets your needs. Of course if the proposed answer meets your needs than why did you bother asking for a solution that didn't loop through the dates? to me it looks like you have accepted an answer which you already knew, or were you just looking for someone to do the coding for you?.
As an alternative you could adopt my suggestion and modify it a bit.
1) find out what day of the week the start and end dates fall on.
2) from the start date loop forward until the next instance of the day the end date falls on (eg if the start day is a wednesday and the end date a monday, count from the start date until the next monday) and count the number of working days in between (using a test as in the junfeb answer). Take this new instance of the day your new start date
3) count the number of days between the new start date and the end date, multiply it by 5/7 to give the number of working days between.
4) add the two counts together to give the total number of days between the dates.
I suspect that if your dates are 12 years apart this might be faster than looping through all the days, you could also bundle it into a function and select it using your SQL statement e.g. SELECT days_between(start_date, end_date) from dual;
Vlad.
ASKER
You are right.
I did accept an unsatisfactory answer but I have been going at this for days and so far every one has told me that I cannot avoid loops if I want to include holidays.
I already had a loop that was similar to the algorithm that was given. It's in PHP/Fi.
What it does is check if the day falls within the weekday.
If it's a weekday then it does a select into a table with dates of hollidays.
If it does not select any rows then $workingdays++
Using your solution then I would just have to do a select of holidays between the 2 dates and have to do a loop of those dates selected to check if they fell on a weekday or not.
But that loop would be minimal.
I did accept an unsatisfactory answer but I have been going at this for days and so far every one has told me that I cannot avoid loops if I want to include holidays.
I already had a loop that was similar to the algorithm that was given. It's in PHP/Fi.
What it does is check if the day falls within the weekday.
If it's a weekday then it does a select into a table with dates of hollidays.
If it does not select any rows then $workingdays++
Using your solution then I would just have to do a select of holidays between the 2 dates and have to do a loop of those dates selected to check if they fell on a weekday or not.
But that loop would be minimal.
take the total number of days MOD by 7 to give you the number of weeks between the two dates, multiply this by five to give the number of working days - all you have to do then is work out how many working days are left in the remainder and add them on. Of course this assumes every week day is a working day and ignore public holidays etc.
Vlad