We help IT Professionals succeed at work.

# Calculating number of working days between two dates

on
8,516 Views
I've got a table which contains a series of dates (the columns are client_id, start_date and end_date). I need to calculate the number of working days between the start and end dates. This count needs to exclude weekends and bank holidays. I've created a table called tbl_holidays which contains a list of all UK bank holidays (the columns are hol_name (eg. xmas day) and hol_date). Can this be done using sql?
Comment
Watch Question

## View Solutions Only

Development Manager
CERTIFIED EXPERT

Commented:
Use following query
``````select client_id, datediff(day,A.start_date,A.end_date) - (Select Count(*) from tbl_holidays where hol_date between A.start_date and A.end_date)
From serieOFDateTable A
``````

Commented:
I dont think oracle 9 supports the DateDiff function so i replaced this part with (end_date - start_date) and this worked (see below). The only problem is that tbl_holiday does not contain the dates of weekends (only bank holidays) and i'm not keen on inputting all the weekend dates into this table! Is there a way to automatically exclude the weekends?

Commented:
forgot to add the updated code
``````select a.client_id,
(A.end_date - a.start_date) - (Select Count(*) from tbl_holidays where hol_date between A.start_date and A.end_date)
From serieOFDateTable A
``````
Development Manager
CERTIFIED EXPERT

Commented:
select a.client_id,
(A.end_date - A.start_date) - (Select Count(*) from tbl_holidays where hol_date between A.start_date and A.end_date) - (A.end_date - A.start_date)/7
From serieOFDateTable A

But
(A.end_date - A.start_date)/7
this will not calculate exact week end days it will be having error of 1 day (+1 or -1).

Even if you want to use if then its fine

If you have one day off then use.
(A.end_date - A.start_date)/7

If you have two days off then use
((A.end_date - A.start_date)/7)*2

I suggest that you should insert your holidies in the table.
Deputy General Manager - IT

Commented:
BELOW EXAMPLE IS COUNTS BOTH START DATE & END DATE EXCLUDING SUNDAY,

SELECT     COUNT(1)
FROM DUAL WHERE  TRIM(TO_CHAR((TO_DATE('26-FEB-08','DD-MON-YY') + LEVEL-1),'DAY'))<>'SUNDAY'
CONNECT BY TO_DATE('26-FEB-08','DD-MON-YY') + LEVEL-1 <= TO_DATE('03-MAR-08','DD-MON-YY')

SYNTAX:
SELECT    COUNT(1)
FROM YOUR_TABLE WHERE  TRIM(TO_CHAR(START_DATE,'DAY'))<>'SUNDAY'
CONNECT BY START_DATE + LEVEL-1 <= END_DATE

u can minus the no of holidays from this
Deputy General Manager - IT

Commented:
or

select count(1) from (
SELECT   START_DATE + LEVEL-1
FROM YOUR_TABLE WHERE  TRIM(TO_CHAR(START_DATE,'DAY'))<>'SUNDAY' and (START_DATE + LEVEL-1) not in (Select trunc(hol_date) from tbl_holidays where hol_date between A.start_date and A.end_date)
CONNECT BY START_DATE + LEVEL-1 <= END_DATE
minus
Select  holdate from tbl_holidays)
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
Deputy General Manager - IT

Commented:
there was an error in my earliesr post

select count(1) from (
SELECT   START_DATE + LEVEL-1
FROM YOUR_TABLE WHERE  TRIM(TO_CHAR(START_DATE,'DAY')) <>('SUNDAY' )
CONNECT BY START_DATE + LEVEL-1 <= END_DATE
minus
Select  holdate from tbl_holidays)
Deputy General Manager - IT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
CERTIFIED EXPERT
Top Expert 2005

Commented:
select client_id, start_date, end_date,
count(*) over ()
- (select count(*) from tbl_holidays where hol_date between t.start_date and t.end_date)
- (select sum(decode(substr(to_char(t.start_date+offset,'Day'),1,1),'S',1,0))
from (select level -1 offset from dual connect by level <= 36500)
where offset <= t.end_date - t.start_date +1)
from yourtable t

Good luck!
CERTIFIED EXPERT
Top Expert 2005
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

Commented:
Thankyou all for your help.
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.