sql query - working days between two dates (including public holidays)

[ronaldmacdonald]

Hi

I am trying to work out the number of working days between 2 dates, including public holidays. Working days are classed as Monday - Friday.

The input two dates are date values stored in two separate tables (U3L.DPL.DATE1 and (U3L.DTK.DATE2). I have a query that produces the number of working days (excluding public holidays) as below:

SELECT U3L.DPL.DATE1,U3L.DTK.DATE2,
(U3L.DTK.DATE2-U3L.DPL.DATE1) -2*FLOOR ((U3L.DTK.DATE2-U3L.DPL.DATE1) /7) - DECODE(SIGN(TO_CHAR(U3L.DTK.DATE2,'D')
- TO_CHAR(U3L.DPL.DATE1,'D')),-1,2,0)+DECODE(TO_CHAR(U3L.DPL.DATE1,'D'),7,1,0)-DECODE(TO_CHAR(U3L.DPL.DATE1,'D'),7,1,0) as Workdays
FROM U3L.DTK,U3L.DPL
WHERE U3L.DPL.KL = U3L.DTK.PKL AND U3L.DTK.TASK_CODE = 'VAL' AND U3L.DPL.DATE1 IS NOT NULL

This returns data of the following type:

DATE1 DATE2 WORKDAYS
--------- ---------- ----------
30-MAY-12 31-MAY-12 1
21-MAY-12 25-MAY-12 4
23-MAY-12 29-MAY-12 4
24-MAY-12 29-MAY-12 3
22-MAY-12 29-MAY-12 5
23-MAY-12 29-MAY-12 4
23-MAY-12 30-MAY-12 5
25-MAY-12 25-MAY-12 0
28-MAY-12
25-MAY-12 30-MAY-12 3
28-MAY-12 30-MAY-12 2
28-MAY-12 30-MAY-12 2

12 rows selected.

Now I have public holidays stored in a separate table U3L.DTK.DATE3.
I am struggling to "link" these dates to my query in order to produce a "WORKDAYS" value that counts the "days" between U3L.DPL.DATE1 and U3L.DTK.DATE2 AND makes a deduction for any days found in this range that are listed in U3L.DTK.DATE3

Cheers

Ronald

 

[SJSFoxPro]

Could you clarify - are you trying to link a third table that contains public holiday records? It looks like you have the public holiday dates in a column of the same table as one of the input dates (U3L.DTK.DATE3)? What are the columns in U3L.DTK and U3L.DPL? (Maybe include a sampling of 3 or more records from each table too.)

 

[ronaldmacdonald]

Could you clarify - are you trying to link a third table that contains public holiday records?

>> Now I have public holidays stored in a separate table U3L.DTK.DATE3 - Yes this is the third table

What are the columns in U3L.DTK

>> just DATE3, which is a list of public holidays in the format dd/mm/yyyy

What are the columns in U3L.DPL?

>> Many, but essentially the date field is DATE1 of format dd/mm/yyyy

I am simply(?!) trying to compare the difference between dates stored in two different tables and make a deduction for any dates found in between these ranges that appear in a third public holday table.

 

[Dagon]

How can U3L.DTK.DATE3 be a third table? Your other tablek is U3L.DTK.DATE2 and DATE2 is clearly the column name, so U3L must be schema and DTK must be the table name.

One possibility would be to do something like this:

create table end_dates (date2 date, id number);

create table start_dates (date1 date, id number);

create table pub_hols (date3 date);

insert into start_dates values (to_date('31-dec-2011', 'DD-MON-YYYY'), 1);
insert into end_dates values (to_date('05-jan-2012', 'DD-MON-YYYY'), 1);
insert into start_dates values (to_date('31-jan-2012', 'DD-MON-YYYY'), 2);
insert into end_dates values (to_date('12-feb-2012', 'DD-MON-YYYY'), 2);
insert into pub_hols values (to_date('01-jan-2012', 'DD-MON-YYYY'));
insert into pub_hols values (to_date('02-jan-2012', 'DD-MON-YYYY'));


select ed.id, ed.date2 - sd.date1 - (select count(1) from pub_hols where date3 between sd.date1 and ed.date2) adj_count
from end_dates ed, start_dates sd
where ed.id = sd.id

 

[ronaldmacdonald]

How can U3L.DTK.DATE3 be a third table? Your other tablek is U3L.DTK.DATE2 and DATE2 is clearly the column name, so U3L must be schema and DTK must be the table name.

I must apologise - you are correct.
U3L is the schema (shared by all 3 tables)
DTK and DPL are the table names.
My original statement would make more sense as follows:
Now I have public holidays stored in a separate table U3L.DTX.DATE3.
I am struggling to "link" these dates to my query in order to produce a "WORKDAYS" value that counts the "days" between U3L.DPL.DATE1 and U3L.DTK.DATE2 AND makes a deduction for any days found in this range that are listed in U3L.DTX.DATE3

 

[SantaMufasa]

Mickey-D,

If I'm not mistaken, your main problem is figuring the number of workdays between two dates, while not counting public holidays and weekend days, right? If so, I recommend just creating an Oracle function the does just that. Then you can use the function anytime you want without complicating your query's driving logic.

Here is an example of, first, using the function in a query to illustrate how simple the query becomes when off-loading the intricacies of holidays-and-weekend-days calculations into the function. (Bear in mind that here in the U.S., Monday, May 28 this year is a public bank holiday (Memorial Day), so since my function uses U.S. holidays, my output differs slighty from your output since is takes that holiday into consideration. But it is also a good test of the function, as well, while using your original data):

select to_char(date1,'Dy, dd-MON-yy') date1
          ,to_char(date2,'Dy, dd-Mon-yy') date2
          ,workdays(date1,date2) workdays
  from macdonald
/

DATE1          DATE2            WORKDAYS
-------------- -------------- ----------
Wed, 30-MAY-12 Thu, 31-May-12          1
Mon, 21-MAY-12 Fri, 25-May-12          4
Wed, 23-MAY-12 Tue, 29-May-12          3
Thu, 24-MAY-12 Tue, 29-May-12          2
Tue, 22-MAY-12 Tue, 29-May-12          4
Wed, 23-MAY-12 Tue, 29-May-12          3
Wed, 23-MAY-12 Wed, 30-May-12          4
Fri, 25-MAY-12 Fri, 25-May-12          0
Mon, 28-MAY-12                         0
Fri, 25-MAY-12 Wed, 30-May-12          2
Mon, 28-MAY-12 Wed, 30-May-12          2
Mon, 28-MAY-12 Wed, 30-May-12          2

12 rows selected.

Here are the support tables that I referenced in the invocation, above:

select * from holidays;

HOLIDATE    DESCRIPTION
----------- ---------------------
02-JAN-2012 New Years Day
16-JAN-2012 MLK Birthday
20-FEB-2012 Washington's Birthday
28-MAY-2012 Memorial Day
04-JUL-2012 Independence Day
03-SEP-2012 Labor Day
08-Oct-2012 Columbus Day
12-NOV-2012 Veterans Day
22-NOV-2012 Thanksgiving Day
25-DEC-2012 Christmas Day

10 rows selected.

Here are the contents of your start and end dates in a table I called MacDonald:

select * from MacDonald;

DATE1     DATE2
--------- ---------
30-MAY-12 31-MAY-12
21-MAY-12 25-MAY-12
23-MAY-12 29-MAY-12
24-MAY-12 29-MAY-12
22-MAY-12 29-MAY-12
23-MAY-12 29-MAY-12
23-MAY-12 30-MAY-12
25-MAY-12 25-MAY-12
28-MAY-12
25-MAY-12 30-MAY-12
28-MAY-12 30-MAY-12
28-MAY-12 30-MAY-12

12 rows selected.

Here, now, are the contents of the function definition, WORKDAYS:

create or replace function workdays (dt1 date, dt2 date) return number is
    weekday_count number := 0;
    beg_dt date := trunc(least(dt1,dt2));
    end_dt date := trunc(greatest(dt1,dt2));
    cur_dt date;
    current_date_is_holiday number;
begin
    if beg_dt = end_dt then
        return 0;
    end if;
    cur_dt := beg_dt;
    while cur_dt <=  end_dt loop
        if cur_dt = beg_dt then 
            null;
        else
            select count(*) into current_date_is_holiday
              from holidays
             where holidate = cur_dt;
            if current_date_is_holiday = 0 then
                if to_char(cur_dt,'DY') not in ('SAT','SUN') then
                    weekday_count := weekday_count+1;
                end if;
            end if;
        end if;
        cur_dt := cur_dt +1;
    end loop;
    return weekday_count;
end;
/

Function created.

You can certainly replace the contents of HOLIDAYS with UK holidays, for as many years as you wish, to more fully test out the logic.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[ronaldmacdonald]

Dagon - your query is ALMOST right - it certainly takes into account the working days listed in the 3rd public holidays table. It doesnt't however account for weekends which would not be working days.

SantaMufasa - your understanding is spot on and your function looks interesting. I havent had time yet to play with it but rest assured I will have a go and report back.

Cheers

RonaldMacdonald

 

[SantaMufasa]

BTW, The Tek-Tips Tech Team is working to resolve the line spacing issue within the CODE tags.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Dagon]

Dagon - your query is ALMOST right - it certainly takes into account the working days listed in the 3rd public holidays table. It doesnt't however account for weekends which would not be working days.

True, but you could easily combine it with the formula you already heave for excluding weekdays. Alternatively, you could populate the weekends into the table as well, which would be very easy to do using a query such as:

select next_day(trunc(sysdate), 'Saturday')+(7*(level-1))
from dual
connect by level <=1000
union all
select next_day(trunc(sysdate), 'Sunday')+(7*(level-1))
from dual
connect by level <=1000
order by 1

 

[ronaldmacdonald]

Dagon, yes - using your solution is now correct. I was able to add weekends going back 10 years which should suffice! Now to procedurize(sp?) this as per SantaMufasa as it is a very useful function for us!

Thx for all your suggestions.
RonaldMacdonald