get work days function to return whole number on sunday

The function number_of_days(start_date, end_date) should calculate how manny work days are in between start_date and end_date. It wroks for whatever start_date as long it is not sunday.

Calculation has 3 steps:

1. take number of days before whole week (whole week can be 0 for small range dates like 21th – 24th)

2. then it adds whole weeks

3. takes all days that are leftover after whole week and add them up.

i need 2nd step to to produce whole number like 0, 5, 10, instead i get 0,7142857142857142857142857142857142857 on this example, because start_date parameter is sunday.

I could use ROUND() to solve it, but perhaps there is a better way?

    CREATE OR REPLACE FUNCTION number_of_days(start_date IN DATE, end_date IN DATE)
    RETURN  NUMBER
    
    IS  v_number_of_days NUMBER;
        first_week_day DATE := TO_DATE('31-12-2017', 'DD-MM-YYYY');
    
    BEGIN
--step 1
    SELECT  ( CASE  WHEN MOD(start_date - first_week_day, 7) BETWEEN 2 AND 5
                    THEN 6 - MOD(start_date - first_week_day, 7)
                    ELSE 0  END )
            +
--step 2
            ((  CASE  WHEN MOD(end_date - first_week_day, 7) < 7
                      THEN end_date - MOD(end_date - first_week_day, 7)
                      ELSE end_date  END )
              -
              ( CASE  WHEN MOD(start_date - first_week_day, 7) > 1
                      THEN start_date + 8 - MOD(start_date - first_week_day, 7)
                      ELSE start_date  END ) + 1
            ) / 7 * 5
            +
--step 3
            ( CASE  WHEN MOD(end_date - first_week_day,7) BETWEEN 1 AND 6
                    THEN CASE   WHEN MOD(end_date - first_week_day, 7) = 6
                                THEN MOD(end_date - first_week_day, 7) - 1
                                ELSE MOD(end_date - first_week_day, 7) END
                    ELSE 0 END  )
    INTO    v_number_of_days
    FROM    DUAL;
    
    RETURN  v_number_of_days;
    
    END;
    

    --test
    SELECT  number_of_days(TO_DATE('21-11-2021', 'DD-MM-YYYY'), TO_DATE('24-11-2021', 'DD-MM-YYYY'))
    FROM    DUAL;

Answer

You’re just making this too complicated… First of all, it’s better to work with in ISO weeks: it starts from Monday.

• https://en.wikipedia.org/wiki/ISO_week_date
• https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm
• https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/TRUNC-date.html#GUID-BC82227A-2698-4EC8-8C1A-ABECC64B0E79

So you can easily get first day of ISO week using trunc(dt, 'IW'). For example, 19th of November is Friday and we can easily get the first day of this week using trunc(date'2021-11-19','IW'):

SQL> select trunc(date'2021-11-19','IW') xx from dual;

XX
-------------------
2021-11-15 00:00:00

So 2021-11-15 is Monday.

Now let’s imagine we got some dates:

 Mo Tu We Th Fr Sa Su
 -- -- -- -- -- -- --
           X  X  X  X
  X  X  X  X  X  X  X
  X  X  X  X  X  X  X
  X  X  X  X  X  X  X
  X  X  X  X  X  X  X
  X  X  X  X  X  X  X
  X  X  X  X  X  X  X
  X  X

We can easily get number of working days in full weeks (count_full_weeks * 5/7), so let’s exclude them:

 Mo Tu We Th Fr Sa Su
 -- -- -- -- -- -- --
           X  X  X  X
  X  X

Then, let’s transform it graphically, since we know that the number of remaining days will always be less than 7 days (less than a week) and the first day will be one of the first 7 days:

 Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa Su
 -- -- -- -- -- -- -- -- -- -- -- -- -- --
           X  X  X  X  X  X

Let’s replace Mo,Tu,We… with their numbers:

  0  1  2  3  4  5  6  0  1  2  3  4  5  6
 -- -- -- -- -- -- -- -- -- -- -- -- -- --
           X  X  X  X  X  X

Since we know that number of days in this partial week can’t be 7, we know that the latest end will be:

  0  1  2  3  4  5  6  0  1  2  3  4  5  6
 -- -- -- -- -- -- -- -- -- -- -- -- -- --
                    X  X  X  X  X  X

Now let’s replace numbers of the second week to 7-13:

  0  1  2  3  4  5  6  7  8  9 10 11 12 13
 -- -- -- -- -- -- -- -- -- -- -- -- -- --
                    X  X  X  X  X  X

Now we can understand that we need to check just for 5th and 6th days, ie to check if our period contains 5 and 6.

So it will be like this (I’ve detailed as much as possible):

CREATE OR REPLACE FUNCTION number_of_days(start_date IN DATE, end_date IN DATE)
    RETURN  NUMBER
as
    days_between        int;
    full_weeks          int;
    remaining_days      int;
    remaining_workdays  int;
    
    v_first_week_day    date;
    v_start             int;
    v_end               int;
    
    result              int;
begin
    days_between := end_date - start_date;
    full_weeks := trunc(days_between/7);
    remaining_days:= mod(days_between,7); -- or days_between-full_weeks*7
    
    v_first_week_day := trunc(start_date,'IW');
    v_start := start_date - v_first_week_day;
    v_end := v_start + remaining_days - 1;
    
    remaining_workdays := remaining_days
                        - case when 5 between v_start and v_end then 1 else 0 end
                        - case when 6 between v_start and v_end then 1 else 0 end
                        ;
    result := full_weeks * 5 + remaining_workdays;
    RETURN result;
end;
/

I did it with a lot of sub-steps and intermediate calculations and variables, just to make it more clear. Obviously, you can make it much shorter, for example:

CREATE OR REPLACE FUNCTION number_of_days(start_date IN DATE, end_date IN DATE)
    RETURN  NUMBER
as
begin
    return 5*trunc((end_date-start_date)/7) + mod((end_date-start_date),7) 
     - case when 5-(start_date - trunc(start_date,'IW')) between 0 and mod((end_date-start_date),7)-1 then 1 else 0 end
     - case when 6-(start_date - trunc(start_date,'IW')) between 0 and mod((end_date-start_date),7)-1 then 1 else 0 end
    ;
end;
/