asked on

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

Sean Stuber

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

dprasad

ASKER

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.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dprasad

ASKER

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

Sean Stuber

glad I could help

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

dprasad

ASKER

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