Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Number of working days between two date fields

Status
Not open for further replies.

Preshit

MIS
Nov 14, 2019
1
IN
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
 
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


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top