1/24/10

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);

4 comments:

  1. Excellent one, thanks for the queries.

    ReplyDelete
  2. This is excellent stuff and I think at least part of the answer to what I'm trying to do. My problem is this: I adapted the example you give in #6 above into a peoplecode function to return a date X number of business days from a start date. I feed the function the start date and the number of business days I want to go forward. The SQL statement works fine as a query returning the correct date but errors in peoplecode. What am I missing?
    fred.gravel@lethbridge.ca

    ReplyDelete
  3. @FGravel,
    Please share the code you are using and the error message returned.

    ReplyDelete
  4. Great post and thank you! Had no idea this was even possible with a single SQL.

    Is it possible to write a SQL using a similar logic to #6 or #7 where I can figure out how many days passed (excluding weekends and holidays) since a row added to a table?

    select row_added_dttm, total_business_days_excluding_holidays_weekends_since_row_added_dttm
    from ps_anytable

    select row_added_dttm,( # of days(sysdate - row_Added_dttm) - (business_days + holidays))
    from ps_anytable

    ReplyDelete

Please refrain for marketing messages and unnecessary back links.

Workflow errors on New Hire and Add employment instance

User tries to hire a new person or tries to add a new organization relationship encounters a series of error messages related to workflow...

Trending in this Blog this month