Convert Crystal Aging function to pl/sql

Posted on 2011-05-10
Medium Priority
Last Modified: 2012-05-11
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

Question by:dprasad
  • 3
  • 3
LVL 74

Expert Comment

ID: 35729409
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


Author Comment

ID: 35729896
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.
LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 35730019
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.


Author Comment

ID: 35730653
Ok cool, that makes sense. Thanks a lot!!
LVL 74

Expert Comment

ID: 35730673
glad I could help

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

Author Comment

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question