Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Angry Unanswered: Please help with changing code

    Hello all,

    I have the following function that adds the number of business days from any date. Looks like it is working fine. This is not my function and was wondering now if someone could help me adjust this to subtract business days rather then adding.

    here is the function
    [code]
    create or replace function add_working_days(
    p_days in number,
    p_dt in date default trunc(sysdate)
    )
    return date
    as
    v_weeks number;
    v_adj number;
    begin

    v_weeks := trunc(p_days/5);

    if to_number(to_char(p_dt,'D')) + mod(p_days,5) >= 7 then
    v_adj := 2;
    else
    v_adj := 0;
    end if;

    return p_dt + 7*v_weeks + v_adj+mod(p_days,5);
    end add_working_days;
    /

    Thanks to everyone for there gracious help.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    Just change the +' to '-'
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2006
    Posts
    140

    Angry

    Thanks for the reply LK_brwn. I should have mentioned that I did that before posting but for some reason some values are correct and others are not. I have provided some returned data that is incorrect. The subtracting number is 7(7 days)


    Date Subtract Date Funcntion result
    Tue Aug 01, 2006 07:36:21 AM 23-JUL-06
    Mon Jul 31, 2006 08:47:59 AM 22-JUL-06
    Mon Aug 07, 2006 07:22:13 AM 29-JUL-06
    Mon Aug 07, 2006 07:33:43 AM 29-JUL-06
    Mon Jul 31, 2006 14:19:45 PM 22-JUL-06
    Mon Aug 14, 2006 10:26:27 AM 05-AUG-06
    Tue Aug 01, 2006 15:18:43 PM 23-JUL-06

    I have changed all instances of a - sign to +. Thanks again.

  4. #4
    Join Date
    Apr 2006
    Posts
    140
    Sorry that should have read all instances of a + to -.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    try a - and a +:

    return p_dt - (7*v_weeks + v_adj+mod(p_days,5));


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Apr 2006
    Posts
    140
    Nope. Still doesn't work. I tried some different variations of + and Minue and still no luck. Any other ideas

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What happens if you call the add_working_days with a -5 instead of a positive 5?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Maybe just use this simple generic function:
    Code:
    CREATE OR REPLACE Function add_business_days
       (start_date_in date, days_in number)
       return date
    IS
       v_counter number;
       v_sign number;
       v_new_date date;
       v_day_number number;
    BEGIN
       v_counter := 1;
       v_sign := SIGN(days_in);
       v_new_date := start_date_in;
       while v_counter <= ABS(days_in)
       loop
          v_new_date := v_new_date + v_sign;
          v_day_number := to_char(v_new_date, 'd');
          if v_day_number not in (1,7) then
             v_counter := v_counter + 1;
          end if;
       end loop;
    RETURN v_new_date;
    
    EXCEPTION
    WHEN OTHERS THEN
       raise_application_error(-20001,'An error was encountered - '||SQLERRM);
    END;
    /


    PS: Correected a previous bug.

    Last edited by LKBrwn_DBA; 08-10-06 at 19:09.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    Apr 2006
    Posts
    140

    Talking

    LKBrwn I owe you one of Thanks so much. Even though we didn't get to the bottom of the correcting the function I'll take your function instead I'm all for whatever works Thanks so much for your help. Just so we are clear to subtract business days I changed 1 line in your function to read
    Code:
    v_new_date := v_new_date - v_sign;
    Instead of the original
    Code:
     v_new_date := v_new_date + v_sign;
    I tested and looks good. Is that correct or should I have to change more? Your functions works with adding but not subtracting.

    beilstwh thanks for the reply. I'm gonna use this function instead since it has been tested and works great. Thanks again.

  10. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    No need to change the code, if you supply a negative number of days, it will subtract those business days otherwise it will add the days. That is why it's generic.

    Good luck!

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  11. #11
    Join Date
    Apr 2006
    Posts
    140

    Talking

    Perfect. Thanks so much for you help. I'm sure I'll be talking to you on this forum in the near future Have a good day.

  12. #12
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs up


    Glad to be of some help.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  13. #13
    Join Date
    Apr 2006
    Posts
    140

    Unhappy Trying to reopen this question as code is freezing when run under sql developer

    hello LK_Brwn_DBA or anyone else who could help,

    I just wanted to reopen this issue. I had to use your generic function again in another report and for some reason when I use the function and run the code i.e.
    Code:
    select add_business_days(convert_utc_date(ConDate,'EDT'),-1) from Table1;
    it returns the rows of data but as I scroll down the list I get to a point where sql Oracle SQL developer freezes. So I thought maybe it was an issue with oracle SQL Developer. However when I run the completed package(as it compiles fine) in crystal reports I get the same freezing. Any ideas where the bottleneck is. I'm assuming something in the code. Thanks again.

    Here is the code
    Code:
    CREATE OR REPLACE
    Function add_business_days
       (start_date_in date, days_in number)
       return date
    IS
       v_counter number;
       v_sign number;
       v_new_date date;
       v_day_number number;
    BEGIN
       v_counter := 1;
       v_sign := SIGN(days_in);
       v_new_date := start_date_in;
       while v_counter <= ABS(days_in)
       loop
          v_new_date := v_new_date + v_sign;
          v_day_number := to_char(v_new_date, 'd');
          if v_day_number not in (1,7) then
             v_counter := v_counter + 1;
          end if;
       end loop;
    RETURN v_new_date;
    END;

  14. #14
    Join Date
    Apr 2006
    Posts
    140

    Angry I think I have figured out the problem but I have no solution

    I think the issue of why it is freezing is because some field values in the database table are blank. How do I modify the code for blank values. Any ideas. Thanks again experts.

  15. #15
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Test within the function. I am assuming that you want to return the current date. You could also return null, or a specific date in the future. Your choice.

    Code:
     CREATE OR REPLACE
    Function add_business_days
         (start_date_in date, days_in number)
       return date
    IS
      v_counter number;
      v_sign number;
      v_new_date date;
      v_day_number number;
    BEGIN
       if start_date_in is null or days_in is null then
      return trunc(sysdate);
       end if;
       v_counter := 1;
       v_sign := SIGN(days_in);
       v_new_date := start_date_in;
       while v_counter <= ABS(days_in)
       loop
      v_new_date := v_new_date + v_sign;
      v_day_number := to_char(v_new_date, 'd');
      if v_day_number not in (1,7) then
        v_counter := v_counter + 1;
      end if;
       end loop;
    RETURN v_new_date;
    END;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •