Question: I need to write an sql query that returns the number of Working days (Monday - Friday) between two given dates.

I was wondering what would be the most efficient way to do this?

```
SELECT           --Start with total number of days including weekends             
(DATEDIFF(dd,@StartDate,@EndDate)+1) --Subtact 2 days for each full weekend 
(DATEDIFF(wk,@StartDate,@EndDate)*2) --If StartDate is a Sunday, Subtract 1          
ELSE 0               END)            --If EndDate is a Saturday, Subtract 1 
FROM dual

```

Then it would also be helpful to be able to remove holidays from this count such as christmas day and boxing day.

Any Ideas?

Answer: 
Thats so simple :

```
    SQL> Select count(*)
      2  from ( select rownum rnum
      3          from all_objects
      4        where rownum <= to_date('18-dec-2009','dd-mon-yyyy') - 
    to_date('16-nov-2009')+1 )
      5    where to_char( to_date('16-nov-2009','dd-mon-yyyy')+rnum-1, 'DY' )
      6                not in ( 'SAT', 'SUN' )

      COUNT(*)
    ----------
            25

    SQL> Select to_char( to_date('16-nov-2009','dd-mon-yyyy')+rnum-1, 'DY dd-mon-yyyy' )
      2  from ( select rownum rnum
      3          from all_objects
      4        where rownum <= to_date('18-dec-2009','dd-mon-yyyy') - to_date('16-nov-2009')+1 )
      5    where to_char( to_date('16-nov-2009','dd-mon-yyyy')+rnum-1, 'DY' )
      6                not in ( 'SAT', 'SUN' )

DAY_DATE
---------------
MON 16-nov-2009
TUE 17-nov-2009
WED 18-nov-2009
THU 19-nov-2009
FRI 20-nov-2009
MON 23-nov-2009
TUE 24-nov-2009
WED 25-nov-2009
THU 26-nov-2009
FRI 27-nov-2009
MON 30-nov-2009
TUE 01-dec-2009
WED 02-dec-2009
THU 03-dec-2009
FRI 04-dec-2009
MON 07-dec-2009
TUE 08-dec-2009
WED 09-dec-2009
THU 10-dec-2009
FRI 11-dec-2009
MON 14-dec-2009
TUE 15-dec-2009
WED 16-dec-2009
THU 17-dec-2009
FRI 18-dec-2009

25 rows selected.

```
This is how i do it, assuming you already got a calendar table with acolumn which indicates if a day is working day or not:
Add a new column to your calendar table like workday\_num and populate it once with a running number using

```
sum(case when workingday then 1 else 0 end)
over (order by calendardate rows unbounded preceding)

```

Now it's two joins to your calendar and a simple difference of the workday\_nums of p\_start\_date and p\_end\_date.