Solved

# Convert Crystal Aging function to pl/sql

Posted on 2011-05-10
686 Views
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
``````
0

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'
)
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 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.
0

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

Author Comment

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

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
0

Author Comment

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

## Join & Write a Comment Already a member? Login.

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.

#### Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!