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

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

Open in new window

0
 
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.
0
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!!
0
 
sdstuberCommented:
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
 
dprasadAuthor 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.

All Courses

From novice to tech pro — start learning today.