Header Ads

  • Recent Post

    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.

    Post Top Ad

    Post Bottom Ad