asked on

Function to adjust weekend and Holiday in Oracle SQL

Hello,

I have a requirement to calculate due date based on the condition below. Could someone please assist on how to create a function?

Thank you

AdjustDtForFedHoliday (Input Date, Type) {
If Type = Forward {
           If the Input Date falls on weekend then
                 Move the date to following Monday.
Else
Keep the date as is.
If derived date after weekend calculation is a federal holiday (Check if the date is in the table Holiday)
Move the date one day forward and perform the check again to make sure adjusted date is not a weekend or federal holiday.
Else
Keep the date as is.
}
If AdjustmentType = Backward {
If the Input Date falls on weekend then
                 Move the date to Friday before.
Else
Keep the date as is.
If derived date after weekend calculation is a federal holiday (Check if the date is in the table Holiday )
Move the date one day back and perform the check again to make sure adjusted date is not a weekend or federal holiday.
Else
Keep the date as is.
      }}

ASKER CERTIFIED SOLUTION

flow01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

Sorry. My code had a bug so I deleted it. It was almost exactly what flow01 had posted.

I purposely didn't look at their code before writing mine. I should have looked at it before posting... sorry.

angel7170

ASKER

This is exactly what I looking for. Thank you so much