We help IT Professionals succeed at work.

Calculating number of working days between two dates

8,516 Views
Last Modified: 2013-12-19
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

Muhammad KashifDevelopment 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

Open in new window

Author

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?

Author

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

Open in new window

Muhammad KashifDevelopment 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.
Shaju KumbalathDeputy 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
Shaju KumbalathDeputy 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)
Sean StuberDatabase 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)
UNLOCK SOLUTION
Shaju KumbalathDeputy 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)
Shaju KumbalathDeputy General Manager - IT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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)
UNLOCK SOLUTION

Author

Commented:
Thankyou all for your help.
Unlock the solution to this question.
Join our community and discover your potential

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.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.