Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    May 2002
    Posts
    18

    Unanswered: Special and Tricky Date difference

    Hi all Gurus there,
    I have a tricky problem in my application. Let me tell the core problem alone. I need the number of days between any two dates excluding saturdays and sundays. For eg. 6 Feb 2003 and 11 Feb 2003. The number of days here will be 3 since we exclude saturdays and sundays.

    Now a bigger picture. My database table in Oracle 8i contains a date column. There are about half a million rows in that table and more is expected. For all the rows, I need to find the number of days between current date and the date in the column excluding saturdays and sundays.

    Initially I thought it is simple but when I verified my solution with various input, I find different conditions and situations and I could not solve it.

    Gurus out there, please help me.

  2. #2
    Join Date
    Jan 2003
    Location
    Vienna, Austria
    Posts
    102

    Re: Special and Tricky Date difference

    do you want something like

    select count(*) from
    (select to_char(date_field, 'DAY) DAY, a.* from table)
    where DAY in ...
    and date_field between ...

    I hope this helps?
    ^/\x

  3. #3
    Join Date
    May 2002
    Posts
    18

    Re: Special and Tricky Date difference

    I dont understand that query. Let me present the problem in a different way. For all the records, I need to get the difference between the current date and the db record date column. This difference should exclude all saturday and sundays.

    Say for a record in the database, the date is
    10 Feb 2003. Today's date is 17 feb 2003. Look at the calendar now. Start including the days one by one from 10 Feb 2003 until 17 feb 2003. If you see saturday or sunday do not include it in your count. So the required output is 5 days. See here the actual difference is 7 days but the required output is 5 because there is a saturday and sunday between the dates.



    Originally posted by berxh3g
    do you want something like

    select count(*) from
    (select to_char(date_field, 'DAY) DAY, a.* from table)
    where DAY in ...
    and date_field between ...

    I hope this helps?

  4. #4
    Join Date
    Jan 2003
    Location
    Vienna, Austria
    Posts
    102

    Re: Special and Tricky Date difference

    which part of the query is unclear? (apart from the type-errors)

    to make it a little bit mor clear:

    select count(*) from
    (select to_char(sysdate-1, 'DAY') DAY, a.* from dual a)
    where NOT DAY in ('SATURDAY ','SUNDAY ')
    and date_field between ...

    just replace 'sysdate-1' and 'dual' in line2 and edit the 'between ...' to useful values.
    ^/\x

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Special and Tricky Date difference

    Originally posted by berxh3g
    which part of the query is unclear? (apart from the type-errors)

    to make it a little bit mor clear:

    select count(*) from
    (select to_char(sysdate-1, 'DAY') DAY, a.* from dual a)
    where NOT DAY in ('SATURDAY ','SUNDAY ')
    and date_field between ...

    just replace 'sysdate-1' and 'dual' in line2 and edit the 'between ...' to useful values.
    Actually, I don't understand it either. The select from DUAL will only return one date or none, so the count will be 1 or 0.

    There is a solution to this problem on this site: http://www.oracledba.co.uk/

    Go to the Miscellaneous section and look for "Business Days"

  6. #6
    Join Date
    Jan 2003
    Location
    Vienna, Austria
    Posts
    102

    Re: Special and Tricky Date difference

    i used 'dual' as I doesn't know about your table, structure, data, ....

    my SQL is not a stable final product, it's just a hint how you can get your target.

    I especially wrote you have to replace the 'dual' and also the 'sysdate -1'
    also the statement 'between ...' isn't complete.
    SQL
    my example is basically near equal to the link you posted.
    the basic hint is
    to_char(day,'day')
    everything else is only simple
    ^/\x

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Special and Tricky Date difference

    Originally posted by berxh3g
    my example is basically near equal to the link you posted.
    Well, kind of. Your example assumes that Mohamed's table which should replace DUAL has one (and only one) record in it for every business date between a given date and today's date. That will probably not be the case. To give the simplest possible example, if his table had just ONE record in it with a date of 10 Feb 2003, and today is 17 Feb 2003, then he wants the answer to be 5, not 1. So the correct solution cannot involve counting records from his table.

    Your solution showed how to determine the day of the week for each date in the table, but not how to count the business days between two dates. There is a big leap from one to the other.

  8. #8
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Maybe this is what you're after, count days excluding Saturdays and Sundays between two given dates. I haven't tested it very hard, but it looks okay. If you want to run it productive you should add some range checks, optimise using integers rather than number etc. I'm sure there is a more elegant solution out there aswell :-)

    See attached SQL for the package. It keeps the loops to a reasonable minimum no matter how large the date range.

    select working_days.daycount( to_date('01/01/2003','DD/MM/YYYY'), to_date('28/02/2003','DD/MM/YYYY') ) from dual

    You could also run it over your table.

    HTH
    Bill
    Attached Files Attached Files

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Since my contribution so far has been just to indicate a solution posted on another website, I thought I'd try to come up with my own solution. I quite like it - it is very simple, and runs quicker than the other two:

    Code:
    CREATE OR REPLACE FUNCTION bdays( d1 DATE, d2 DATE ) RETURN NUMBER
    IS
      v_num_full_weeks INTEGER;
      v_num_odd_days   INTEGER;
      v_bdays          INTEGER;
    BEGIN
      v_num_full_weeks := TRUNC((d2-d1)/7);
      v_num_odd_days   := (d2-d1) - v_num_full_weeks*7;
      v_bdays          := v_num_full_weeks*5;
      FOR i IN 1..v_num_odd_days LOOP
        IF TO_CHAR( d1-1+i, 'D') NOT IN (1,7) THEN
          v_bdays := v_bdays+1;
        END IF;
      END LOOP;
      RETURN v_bdays;
    END;
    /
    What it does is:
    1) Count the number of full weeks between the two dates. Each of these is worth 5 business days.
    2) Get the number of remaining days after all full weeks have been eliminated.
    3) Inspect the first (or could equally have checked the last) n days (where n is the result from step 2) to see if they are business days or not. Add 1 to the total for each one that is a business day.

    Now I created a test that runs and times:
    1) The solution from www.oracledba.co.uk
    2) Bill's package solution
    3) My solution above

    Code:
    DECLARE
      v_days INTEGER;
      v_start_time NUMBER;
      v_end_time NUMBER;
    begin
      v_start_time := DBMS_UTILITY.GET_TIME;
      for i in 1..100 loop
        v_days :=  count_bdays( '10-feb-2003', TRUNC(SYSDATE) );
      end loop;
      v_end_time := DBMS_UTILITY.GET_TIME;
      dbms_output.put_line('Method 1 Days = ' || TO_CHAR(v_days,'9999') || '  Time = ' || TO_CHAR(v_end_time-v_start_time) );
    
      v_start_time := DBMS_UTILITY.GET_TIME;
      for i in 1..100 loop
        v_days :=  working_days.daycount( '10-feb-2003', SYSDATE );
      end loop;
      v_end_time := DBMS_UTILITY.GET_TIME;
      dbms_output.put_line('Method 2 Days = ' || TO_CHAR(v_days,'9999') || '  Time = ' || TO_CHAR(v_end_time-v_start_time) );
    
      v_start_time := DBMS_UTILITY.GET_TIME;
      for i in 1..100 loop
        v_days :=  bdays( '10-feb-2003', SYSDATE );
      end loop;
      v_end_time := DBMS_UTILITY.GET_TIME;
      dbms_output.put_line('Method 3 Days = ' || TO_CHAR(v_days,'9999') || '  Time = ' || TO_CHAR(v_end_time-v_start_time) );
    
    end;
    /
    The results I get are as follows:

    Method 1 Days = 6 Time = 85
    Method 2 Days = 6 Time = 7
    Method 3 Days = 5 Time = 3

    When I change the first date to 01-JAN-2001 the results are:

    Method 1 Days = 556 Time = 1836
    Method 2 Days = 556 Time = 8
    Method 3 Days = 555 Time = 3

    And for 01-JAN-1991:

    Method 1 Days = 3165 Time = 10110
    Method 2 Days = 3165 Time = 7
    Method 3 Days = 3164 Time = 5

    So my conclusion is that the solution I originally suggested (method 1) is actually too slow to be useful in practise, and that my function has a small edge over Bill's package.

    Note: my function is also returning a value 1 less than the other two. This is because I consider that there are 5 business days between 10-feb and 17-feb, not 6. Obviously, the function can easily be modified to suit your own preference!

    Also, remember that we are not taking into account public holidays - any of the solutions can easily be modified to count the public holidays from a holiday table and subtract them from the total.

  10. #10
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Andrew,

    I don't think you can lump the loose days (either side of the good full week range) into one for the day count IYSWIM :-)

    I've just run Method 2 and Method 3 over the date range

    3rd Feb 2003 to 21st Feb 2003 and got these...

    Method 2 - 15
    Method 3 - 13

    and for 4th Feb to 21st Feb
    Method 2 - 14
    Method 3 - 13

    As both the 3rd and 4th Feb are work days, your method isn't quite right somewhere. I didn't look in detail, but I think you need to count the loosee days either side of the range separately....

    HTH
    Bill

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, there was one problem with my function - I had lazily omitted to TRUNC the date parameters, meaning that the day count went up by 1 after midday. But even so, I don't get the results you get - I think you must have made a mistake in your tests.

    Here is my corrected function:
    Code:
    CREATE OR REPLACE FUNCTION bdays( p_d1 DATE, p_d2 DATE ) RETURN NUMBER
    IS
      v_d1             DATE := TRUNC( p_d1 );
      v_d2             DATE := TRUNC( p_d2 );
      v_num_full_weeks INTEGER;
      v_num_odd_days   INTEGER;
      v_bdays          INTEGER;
    BEGIN
      v_num_full_weeks := TRUNC((v_d2-v_d1)/7);
      v_num_odd_days   := (v_d2-v_d1) - v_num_full_weeks*7;
      v_bdays          := v_num_full_weeks*5;
      FOR i IN 1..v_num_odd_days LOOP
        IF TO_CHAR( v_d1-1+i, 'D') NOT IN (1,7) THEN
          v_bdays := v_bdays+1;
        END IF;
      END LOOP;
      RETURN v_bdays;
    END;
    /
    And here is the test script, updated to show the dates being tested:

    Code:
    DECLARE
      v_days INTEGER;
      v_start_time NUMBER;
      v_end_time NUMBER;
      v_date1 DATE := '03-feb-2003';
      v_date2 DATE := '21-feb-2003';
    begin
      dbms_output.put_line( 'Test run for '||v_date1||' to '||v_date2);
    
      v_start_time := DBMS_UTILITY.GET_TIME;
      for i in 1..100 loop
        v_days :=  count_bdays( v_date1, v_date2 );
      end loop;
      v_end_time := DBMS_UTILITY.GET_TIME;
      dbms_output.put_line('Method 1 Days = ' || TO_CHAR(v_days,'9999') || '  Time = ' || TO_CHAR(v_end_time-v_start_time) );
    
      v_start_time := DBMS_UTILITY.GET_TIME;
      for i in 1..100 loop
        v_days :=  working_days.daycount( v_date1, v_date2 );
      end loop;
      v_end_time := DBMS_UTILITY.GET_TIME;
      dbms_output.put_line('Method 2 Days = ' || TO_CHAR(v_days,'9999') || '  Time = ' || TO_CHAR(v_end_time-v_start_time) );
    
      v_start_time := DBMS_UTILITY.GET_TIME;
      for i in 1..100 loop
        v_days :=  bdays( v_date1, v_date2 );
      end loop;
      v_end_time := DBMS_UTILITY.GET_TIME;
      dbms_output.put_line('Method 3 Days = ' || TO_CHAR(v_days,'9999') || '  Time = ' || TO_CHAR(v_end_time-v_start_time) );
    
    end;
    /
    Now here are the results:

    Test run for 03-FEB-2003 to 21-FEB-2003
    Method 1 Days = 15 Time = 99
    Method 2 Days = 14 Time = 6
    Method 3 Days = 14 Time = 2

    Test run for 04-FEB-2003 to 21-FEB-2003
    Method 1 Days = 14 Time = 99
    Method 2 Days = 13 Time = 5
    Method 3 Days = 13 Time = 2

    There is no flaw in my logic re the "loose" days. If you take out all the "whole" weeks from the range, the remaining days will contain the same number of weekend days regardless of whether they are at the beginning, middle or end of the range - they must do, because the total number of weekend days is constant, and so is the number of weekend days in N weeks.
    So using 03-Feb to 21-Feb as the example, that is 18 days (excluding the last day), which is 2 weeks + 4 days
    2 weeks will contain 10 business days.
    If we take out the first 2 weeks, the remaining 4 days are 18,29,20,21 which are Tue,Wed,Thu,Fri = 4 weekdays
    If we take out the last 2 weeks, the remaining 4 days are 3,4,5,6 which are Mon.Tue,Wed,Thu = 4 weekdays
    If we (perversely) take out the first and last week, the remaining days are 10,11,12,13 which are Mon.Tue,Wed,Thu = 4 weekdays

  12. #12
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Andrew,

    Here is the test block, note that your method seems to be between 2 and 0 days out (ie possibly up to 2 days or I would call it "up to one weekend"). As you mentioned earlier, your method should return 1 day less than mine, but I'm getting random results of 2 days less, 1 day less and identical.

    declare
    nMeth2Days number;
    nMeth3Days number;
    dStart date default to_date('01/01/2003','DD/MM/YYYY');
    dEnd date default to_date('02/01/2003','DD/MM/YYYY');
    begin
    for i in 1..1000 loop
    nMeth2Days := working_days.daycount( dStart,dEnd );
    --
    nMeth3Days := bdays( dStart,dEnd );
    --
    if nMeth3Days <> (nMeth2Days-1) THEN
    dbms_output.put_line( dStart||' to '||dEnd||' Method2 '||nMeth2Days||' Method3 '||nMeth3Days );
    END IF;
    dStart := dStart+1;
    dEnd := dEnd+2;
    end loop;
    end;

    The results show over 50% errors in the 1000 date ranges applied (I won't post them all here!) but the first clear one is the 5th Jan to the 10th Jan 2003 (Sunday to Friday). Mine shows 5 working days (correct), yours shows 3 working days (incorrect it should show 4).

    Cheers
    Bill

  13. #13
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    [SIZE=1]Originally posted by andrewst

    There is no flaw in my logic re the "loose" days. If you take out all the "whole" weeks from the range, the remaining days will contain the same number of weekend days regardless of whether they are at the beginning, middle or end of the range - they must do, because the total number of weekend days is constant, and so is the number of weekend days in N weeks.
    If my quick thoughts are right, the total loose from beginning and end days could be between 0 and 12.

    You have to check the actual loose days at the beginning and the loose days at the end individually against the actual day of week for each because if it's say a total of 4 loose days, they could all be at the end running fri to mon (=2 work days) or all at the beginning running mon-thu (=4 work days) or be split either side of the actual range.

    I'm sure that isn't going to make any sense to me if I read it again in a few minutes time :-)

    Cheers
    Bill

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I don't know what's happening here - we seem to be running different functions!

    Here are the first few lines I get when I run your test:

    02-JAN-2003 to 04-JAN-2003 Method2 1 Method3 2
    04-JAN-2003 to 08-JAN-2003 Method2 4 Method3 2
    06-JAN-2003 to 12-JAN-2003 Method2 5 Method3 5
    09-JAN-2003 to 18-JAN-2003 Method2 6 Method3 7
    11-JAN-2003 to 22-JAN-2003 Method2 9 Method3 7

    There is no result for 5th to 10th of Jan, so I ran that separately:

    SQL> select bdays('05-jan-2003','10-jan-2003') from dual
    2 /

    BDAYS('05-JAN-2003','10-JAN-2003')
    ----------------------------------
    4

    (not 3 as you are getting)

    Now, I am standing by my results! Look at 11th to 22nd. I make it 7, you make it 9 - yours is wrong!?

    What are your first 5 results showing exactly?
    And what does your copy of my function look like (it's clearly different to the one I posted!)

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Bill,

    I think we may have a culture difference (NLS_LANGUAGE???) here. Your package has Monday = day 1, whereas for me:

    SQL> select to_char(sysdate,'DY D') from dual
    2 /

    TO_CH
    -----
    MON 2

    My week starts on a Sunday, so my function assumes that days 1 and 7 are weekend days, whereas you are (I think) using days 6 and 7, which are Friday and Saturday for me. In which case, you would need to modify my function here, from:
    Code:
    IF TO_CHAR( v_d1-1+i, 'D') NOT IN (1,7) THEN
    to
    Code:
    IF TO_CHAR( v_d1-1+i, 'D') NOT IN (6,7) THEN

Posting Permissions

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