Number of working days between two date fields

[Preshit]

Hi Dnoeth (Instructor),

Had refer your code to calculate working days, want to include holidays in the same how do i do that?


select
/** The number of whole weeks * 5 **/
((enddate - startdate + 1) / 7) * 5 +

/** the number of remaining days, that don't fall on Saturday or Sunday **/
(case when (enddate - startdate + 1) mod 7 < 1
or (((startdate - DATE '0001-01-01') mod 7) + 1) in (6,7)
then 0 else 1 end) +
(case when (enddate - startdate + 1) mod 7 < 2
or (((startdate + 1 - DATE '0001-01-01') mod 7) + 1) in (6,7)
then 0 else 1 end) +
(case when (enddate - startdate + 1) mod 7 < 3
or (((startdate + 2 - DATE '0001-01-01') mod 7) + 1) in (6,7)
then 0 else 1 end) +
(case when (enddate - startdate + 1) mod 7 < 4
or (((startdate + 3 - DATE '0001-01-01') mod 7) + 1) in (6,7)
then 0 else 1 end) +
(case when (enddate - startdate + 1) mod 7 < 5
or (((startdate + 4 - DATE '0001-01-01') mod 7) + 1) in (6,7)
then 0 else 1 end) +
(case when (enddate - startdate + 1) mod 7 < 6
or (((startdate + 5 - DATE '0001-01-01') mod 7) + 1) in (6,7)
then 0 else 1 end) as workingdays
from
(
select
date '2003-01-06' as startdate,
date '2003-01-17' as enddate
) dt

Preshit

 

[johnherman]

Working days? Are there never any Holidays? No one ever works on Saturday or Sunday? If so, you really just want to count MTWTF days and not Sat/Sun. a.k.a. weekdays.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity