CODEWITHSUNDEEP
oracle-databasefunctiondate
Gerben
Gerben

Reputation: 117

date + 7 working days

I need to write a function that will give me a new due date for an invoice. This needs to be 12 working days after the current due date Say the current due date is 01.Oct.2014. If I look at my calendar manually, I can see that the new date would be 17.Oct.2014 (need to exclude weekends).

However, I also have a table with Bank Holidays. This would have to be taken into consideration. So if I would have a Bank Holiday on 04.Oct.2014, the new due date should be 18.Oct.2014.

EDIT: My table with Bank Holidays would look something like this:

Year: Date: Description

2014 04.Oct.2014 Bank Holiday 1

Any help with this would be deeply appreciated, I'm stuck at this for almost a day now.

Thanks a lot in advance.

Kind regards

Gerben

Upvotes: 1

Views: 183

Answers (2)

Gerben
Gerben

Reputation: 117

I ended up doing things slightly different. I have a table with all my bank holiday. I created a second table as a kind of calendar. In here, I loaded all dates in a year. I then flag it as weekend or bank holiday (2 separate columns).

I take my original due date, and add the 12 days. I then have a start and end date (v_due_date_old and v_due_date_new)

After that, I count how many days there are in my 'calendar' table, where either my flag for weekend or bank holiday is set to Yes. If v_due_date_new is on a Saturday, I add another day to my count.

I then add the new count to v_due_date_new.

As a last step, I check what day v_due_date_new is. If it is Saturday or Sunday, I add another 2 days

Upvotes: 0

Bart van der Drift
Bart van der Drift

Reputation: 1336

Something like this should work:

DECLARE
  l_date DATE := SYSDATE;

  FUNCTION IS_WEEKEND(P_DATE IN DATE)
    RETURN BOOLEAN
  IS
    l_daynum VARCHAR2(1) := to_char (P_DATE, 'D');
  BEGIN
    RETURN l_daynum = '6' OR l_daynum = '7';
  END;

  FUNCTION IS_HOLIDAY(P_DATE IN DATE)
    RETURN BOOLEAN
  IS
    CURSOR c_exists IS
      SELECT 1 FROM bank_holidays WHERE date = TRUNC(P_DATE)
    ;
    l_count NUMBER;
  BEGIN
    OPEN c_exists;
    l_count := c_exists%ROWCOUNT;
    CLOSE c_exists;
    RETURN l_count > 0;
  END;

  PROCEDURE ADD_WORKING_DAYS(P_DATE IN OUT DATE, P_DAYS IN NUMBER)
  IS
    l_workdays_added NUMBER := 0;
  BEGIN
    WHILE TRUE
    LOOP
      P_DATE := P_DATE + 1;
      IF NOT IS_WEEKEND(P_DATE) AND NOT IS_HOLIDAY(P_DATE) THEN
        l_workdays_added := l_workdays_added + 1;
      END IF;
      IF l_workdays_added = P_DAYS THEN
        RETURN;
      END IF;
    END LOOP;
  END;

BEGIN
  ADD_WORKING_DAYS(l_date, 12);
END;

Upvotes: 2

Related Questions