Convert Crystal Aging function to pl/sql

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

Who is Participating?
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
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;
      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

dprasadAuthor Commented:
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dprasadAuthor Commented:
Ok cool, that makes sense. Thanks a lot!!
glad I could help

rather than accepting your own comment,  just accept the post(s) that actually helped and the question will close immediately
dprasadAuthor Commented:
Whoops hit wrong button, meant to accept sdstuber's answer for 500 pts
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.