Calculating number of working days between two dates

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

Select allOpen in new window

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?

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

Select allOpen in new window

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.

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

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)

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)

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!

Thankyou all for your help.