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

Commented:
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
etc

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
0

Commented:
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'
)
RETURN INTEGER
IS
v_cnt   INTEGER;
BEGIN
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;
END;
``````
0

Author 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.
0

Author Commented:
Ok cool, that makes sense. Thanks a lot!!
0

Commented:

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

Author Commented:
Whoops hit wrong button, meant to accept sdstuber's answer for 500 pts
0
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.