Finding the 'n'th business day in PeopleSoft application using Oracle SQL query

So often in PeopleSoft, we have the necessity to send notifications after 'n' number of business days. The considerations in calculating 'n' no. of business days are:

• Number of Saturdays and Sundays - Weekend days can be identified using standard ORACLE(can be in any database) functions and removed from a result set. In some countries where Saturday is a working day and Monday a holiday, we can still process the date +/- 'n' calculation using database functions.
• Company specific holidays (not just Country/State/Region specific). PeopleSoft provides a table called PS_HOLIDAY_DATE in which holidays are maintained with a unique field called HOLIDAY_SCHEDULE based on which holidays are keyed in. We have to make use of this table and Oracle(any database) standard functions in PeopleCode and calculate the number of business days or identify the 'n'th business day from a given date.

In some cases such as a SQR or a PS-Query we have to rely purely on SQL to calculate. In PS-Query it can get little complex without the use of standard programming functions and loops which  PeopleCode and SQRs provide. We have to write one single SQL to get the desired results as we do not have the option of a Stored Procedure in a PS-Query. Though not a trivial SQL with some patience one can write the SQL. I was able to write such a SQL and I am sure there could be much better ways to attain the same result with an even simple SQL. After all, no query is optimal and so is mine.

Through out this post, all dates hardcoded in the SQL queries are the input dates which must be passed at run-time. The desired result for us is to fetch all hires just 10 days prior to a given date from JOB record. To achieve this, first we need to find out the 10th business day from a given date. I have listed all the SQL queries I used iteratively from the scratch to reach to the desired 'n'th business day.

1) Query which gives day of a date in DAY format:
 SELECT to_char(sysdate+10,'DY'),sysdate from dual);

2) Query which gives day of a date in day number format:
 SELECT to_char(sysdate+10,'D'),sysdate from dual);

3) Query gives 'n' number of rows for each day from the input date where 'n' is the number in rownum condition:
 SELECT
        TO_DATE ('31-dec-2008', 'DD-MON-YYYY') + rownum the_date
 FROM
        all_objects
 WHERE
        ROWNUM < 10;
 
4) Query to give number of business days excluding Saturday, Sunday and Holidays:
 SELECT COUNT(*)
 FROM (SELECT rownum rnum
              FROM all_objects
              WHERE rownum <= to_date('30-nov-2009') - to_date('25-nov-2009') + 1)
 WHERE to_char(to_date('25-nov-2009') + rnum -1, 'DY')
                                    NOT IN ('SAT', 'SUN')
 AND to_char(to_date('25-nov-2009') + rnum -1)
                                    NOT IN (SELECT to_char(holiday)
                                               FROM ps_holiday_date
                                               WHERE holiday_schedule
                                                             = 'Reqd_Schedule'));

5) Query which lists all holidays between two dates:
 SELECT rnum-1+to_date('22-nov-2009')
 FROM (SELECT rownum rnum
               FROM all_objects
               WHERE rownum <= to_date('30-nov-2009') - to_date('22-nov-2009') + 1)
 WHERE ( to_char(to_date('22-nov-2009') + rnum -1, 'DY')
                                          IN ('SAT', 'SUN'))
                 OR ( to_char(to_date('22-nov-2009') + rnum -1)
                                          IN (SELECT to_char(holiday)
                                          FROM ps_holiday_date
                                          WHERE holiday_schedule
                                                       = 'Reqd_Schedule'));

6) This query gives a date 10 days prior to a given date excluding weekends and holidays:
 SELECT MIN(busdate) AS tendaydate
 FROM (SELECT busdate, busday
             FROM (SELECT to_date('30-Nov-2009', 'DD-MON-YYYY') - rownum + 1 busdate,
                           (to_char(to_date('30-Nov-2009') -rownum + 1, 'DY')) busday
             FROM all_objects
             ORDER BY 1) NO_WKEND
 WHERE to_char(NO_WKEND.busdate, 'DY')
                                     NOT IN ('SAT', 'SUN')
 AND NO_WKEND.busdate
                                     NOT IN (SELECT to_char(holiday)
                                                     FROM ps_holiday_date
                                                     WHERE holiday_schedule
                                                           = 'Reqd_Schedule'))
              ORDER BY 1 DESC) NO_WKEND_HOLID
 WHERE rownum < 11;

Note: In the last line of the SQL, in the condition - 'WHERE rownum < 11' number 11 gives the 10th business day prior to a given date. To get the 30th business day 'use rownum < 31'. The number used here must be 'n'+1 to find the 'n'th business day.

7) This query gives hires from JOB record prior to 10 days (our desired result):
SELECT * from PS_JOB A
WHERE A.HIRE_DT = (SELECT MIN(busdate) AS tendaydate
                              FROM (SELECT busdate, busday
                                       FROM (SELECT to_date('30-nov-2009', 'DD-MON-YYYY') -rownum + 1 busdate, (to_char(to_date('30-nov-2009') -rownum + 1, 'DY')) busday 
                                                  FROM all_objects
                                                 ORDER BY 1) NO_WKEND
                                    WHERE to_char(NO_WKEND.busdate, 'DY') NOT IN ('SAT', 'SUN')
 AND NO_WKEND.busdate
                  NOT IN ((SELECT to_char(holiday)
                                   FROM ps_holiday_date
                                   WHERE holiday_schedule
                                                   = 'Reqd_Schedule'))
ORDER BY 1 DESC)NO_WKEND_HOLID
WHERE rownum < 11);