MySQL Forums
Forum List  »  Stored Procedures

Re: Recursively use a function to exclude Weekends and National Holidays to find a due date
Posted by: Pandit P
Date: March 09, 2019 11:56PM

Hello mr.Peter Brawley,

Thanks for the response.

By using the function I am able to filter the weekends and move to the next working day (Monday).

As you suggested "if I have to aintain a calendar table with a row for every day in the relevant range, then simply count rows between days excluding weekend days & holidays", I need to enter the year wise data in it every year.

Is there any other way, where i can loop through the holidays check again whether next arived date is again weekend and again public holiday, till I finally arrive to a working date which is not a public holiday or Sat. & Sunday.

Currently, the only problem I am facing when I use the function recursively is
I am not able ignore the already checked public holiday date.

For example ;

When I say that my start date is 2019-03-5 and the Working Days target is 5 then it arrives on 2019-03-12 which is a Monday and then it checks whether it is a public holiday since I am checking the date range in the holiday_list table bysing the query

SELECT cont(*) from holiday_list where holiday_date between iStartDate and iEndDate; -- >

-- > First time it gives 1 as the count when the iEnddate is 2019-3-12 and then second time it gives 2 when the date range checked is 2019-03-12 and 2019-03-13. But the date 2019-03-12 is already checked so it should ignore the startdate 2019-03-12, that is where I am getting a wrong date which is finally returned when the function ends recursively.

Please advice.

Thanks and regards

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.