Convert Crystal Aging function to pl/sql

Posted on 2011-05-10
Last Modified: 2012-05-11
Hello. We currently have a Crystal repository function that calculates the age of a record by subtracting out non work days. Does anyone have a good example of how to do something similar to the below in pls/ql?

downdays = days to substract.

calculates number of days between close and open date

Function  (dateTimeVar DT_1, dateTimeVar DT_2, Optional StringVar Proj := "GLBL")
local dateVar X := Date(DT_1);
local dateVar Target := Date(DT_2);
local numberVar DownDays := 0;

While X <= Target
    Do ( If (DayOfWeek(X) in [1,7] OR US_Holidays(X, Proj))
            then DownDays := DownDays + 1
            else DownDays := DownDays + 0;
         X := X + 1
(DateDiff ("h", DT_1, DT_2) / 24) -         // Total number of days (#2)
DownDays                                                // - Holidays (#1)
                                                        // = Number of Business Days

Open in new window

Question by:dprasad
    LVL 73

    Expert Comment

    assuming you have a function us_holiday that will return 1 for Holidays and 0 for work days then try this...
    CREATE OR REPLACE FUNCTION work_days_between(p_date1     IN DATE,
                                                 p_date2     IN DATE,
                                                 p_project   IN VARCHAR2 DEFAULT 'GLBL'
        v_cnt   INTEGER;
        SELECT COUNT(*)
          INTO v_cnt
          FROM (SELECT     p_date1 + (LEVEL - 1) d
                      FROM DUAL
                CONNECT BY p_date1 + (LEVEL - 1) <= p_date2)
         WHERE TO_CHAR(d, 'Dy') NOT IN ('Sat', 'Sun') AND us_holiday(d, p_project) = 0;
        RETURN v_cnt;

    Open in new window


    Author Comment

    That's exactly what I was looking for thanks a lot!!!

    Just one more thing, could you explain how the CONNECT BY is working here? CONNECT BY always confuses me.
    LVL 73

    Accepted Solution

    This is a non standard, but very common usage of CONNECT BY

    normally, connect by is used to generate a hierarchy of parent-child records.

    Here, I'm artificially creating my own hierarchy which effectively makes
    2 a child of 1,
    3 a child of 2,
    4 a child of 3

    I call it artificial since there is no PRIOR record.  It's just a level checking condition.  If level 1 isn't done, then the connect by will generate level 2 and check.

    I add each level minus one (0,1,2,3,4...) to the start date to generate the list of days and when I have all of the days I'm done incrementing levels.  Throw out the weekends and holidays and whatever is left must be the workdays

    Author Comment

    Ok cool, that makes sense. Thanks a lot!!
    LVL 73

    Expert Comment

    glad I could help

    rather than accepting your own comment,  just accept the post(s) that actually helped and the question will close immediately

    Author Comment

    Whoops hit wrong button, meant to accept sdstuber's answer for 500 pts

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now