INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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

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

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.)

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

(OP)
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.

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

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:

CODE

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

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

(OP)
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


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

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):

CODE

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:

CODE

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:

CODE

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:

CODE

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.

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

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

(OP)
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

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

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

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

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

Quote:


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:

CODE

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

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close