But that won't work. What about Holidays?
Joe Celko's book "SQL for Smarties" has a good discussion of this
question on page 74.
Here's a series of INSERT INTO statements for all of the workdays in
2002 excluding US Federal Holidays. I made this list is Excel, per
Joe Celko's suggestion.
Tom Nunamaker
INSERT INTO workdays VALUES ('2002-1-2')
INSERT INTO workdays VALUES ('2002-1-3')
INSERT INTO workdays VALUES ('2002-1-4')
INSERT INTO workdays VALUES ('2002-1-7')
INSERT INTO workdays VALUES ('2002-1-8')
INSERT INTO workdays VALUES ('2002-1-9')
INSERT INTO workdays VALUES ('2002-1-10')
INSERT INTO workdays VALUES ('2002-1-11')
INSERT INTO workdays VALUES ('2002-1-14')
INSERT INTO workdays VALUES ('2002-1-15')
INSERT INTO workdays VALUES ('2002-1-16')
INSERT INTO workdays VALUES ('2002-1-17')
INSERT INTO workdays VALUES ('2002-1-18')
INSERT INTO workdays VALUES ('2002-1-22')
INSERT INTO workdays VALUES ('2002-1-23')
INSERT INTO workdays VALUES ('2002-1-24')
INSERT INTO workdays VALUES ('2002-1-25')
INSERT INTO workdays VALUES ('2002-1-28')
INSERT INTO workdays VALUES ('2002-1-29')
INSERT INTO workdays VALUES ('2002-1-30')
INSERT INTO workdays VALUES ('2002-1-31')
INSERT INTO workdays VALUES ('2002-2-1')
INSERT INTO workdays VALUES ('2002-2-4')
INSERT INTO workdays VALUES ('2002-2-5')
INSERT INTO workdays VALUES ('2002-2-6')
INSERT INTO workdays VALUES ('2002-2-7')
INSERT INTO workdays VALUES ('2002-2-8')
INSERT INTO workdays VALUES ('2002-2-11')
INSERT INTO workdays VALUES ('2002-2-12')
INSERT INTO workdays VALUES ('2002-2-13')
INSERT INTO workdays VALUES ('2002-2-14')
INSERT INTO workdays VALUES ('2002-2-15')
INSERT INTO workdays VALUES ('2002-2-19')
INSERT INTO workdays VALUES ('2002-2-20')
INSERT INTO workdays VALUES ('2002-2-21')
INSERT INTO workdays VALUES ('2002-2-22')
INSERT INTO workdays VALUES ('2002-2-25')
INSERT INTO workdays VALUES ('2002-2-26')
INSERT INTO workdays VALUES ('2002-2-27')
INSERT INTO workdays VALUES ('2002-2-28')
INSERT INTO workdays VALUES ('2002-3-1')
INSERT INTO workdays VALUES ('2002-3-4')
INSERT INTO workdays VALUES ('2002-3-5')
INSERT INTO workdays VALUES ('2002-3-6')
INSERT INTO workdays VALUES ('2002-3-7')
INSERT INTO workdays VALUES ('2002-3-8')
INSERT INTO workdays VALUES ('2002-3-11')
INSERT INTO workdays VALUES ('2002-3-12')
INSERT INTO workdays VALUES ('2002-3-13')
INSERT INTO workdays VALUES ('2002-3-14')
INSERT INTO workdays VALUES ('2002-3-15')
INSERT INTO workdays VALUES ('2002-3-18')
INSERT INTO workdays VALUES ('2002-3-19')
INSERT INTO workdays VALUES ('2002-3-20')
INSERT INTO workdays VALUES ('2002-3-21')
INSERT INTO workdays VALUES ('2002-3-22')
INSERT INTO workdays VALUES ('2002-3-25')
INSERT INTO workdays VALUES ('2002-3-26')
INSERT INTO workdays VALUES ('2002-3-27')
INSERT INTO workdays VALUES ('2002-3-28')
INSERT INTO workdays VALUES ('2002-3-29')
INSERT INTO workdays VALUES ('2002-4-1')
INSERT INTO workdays VALUES ('2002-4-2')
INSERT INTO workdays VALUES ('2002-4-3')
INSERT INTO workdays VALUES ('2002-4-4')
INSERT INTO workdays VALUES ('2002-4-5')
INSERT INTO workdays VALUES ('2002-4-8')
INSERT INTO workdays VALUES ('2002-4-9')
INSERT INTO workdays VALUES ('2002-4-10')
INSERT INTO workdays VALUES ('2002-4-11')
INSERT INTO workdays VALUES ('2002-4-12')
INSERT INTO workdays VALUES ('2002-4-15')
INSERT INTO workdays VALUES ('2002-4-16')
INSERT INTO workdays VALUES ('2002-4-17')
INSERT INTO workdays VALUES ('2002-4-18')
INSERT INTO workdays VALUES ('2002-4-19')
INSERT INTO workdays VALUES ('2002-4-22')
INSERT INTO workdays VALUES ('2002-4-23')
INSERT INTO workdays VALUES ('2002-4-24')
INSERT INTO workdays VALUES ('2002-4-25')
INSERT INTO workdays VALUES ('2002-4-26')
INSERT INTO workdays VALUES ('2002-4-29')
INSERT INTO workdays VALUES ('2002-4-30')
INSERT INTO workdays VALUES ('2002-5-1')
INSERT INTO workdays VALUES ('2002-5-2')
INSERT INTO workdays VALUES ('2002-5-3')
INSERT INTO workdays VALUES ('2002-5-6')
INSERT INTO workdays VALUES ('2002-5-7')
INSERT INTO workdays VALUES ('2002-5-8')
INSERT INTO workdays VALUES ('2002-5-9')
INSERT INTO workdays VALUES ('2002-5-10')
INSERT INTO workdays VALUES ('2002-5-13')
INSERT INTO workdays VALUES ('2002-5-14')
INSERT INTO workdays VALUES ('2002-5-15')
INSERT INTO workdays VALUES ('2002-5-16')
INSERT INTO workdays VALUES ('2002-5-17')
INSERT INTO workdays VALUES ('2002-5-20')
INSERT INTO workdays VALUES ('2002-5-21')
INSERT INTO workdays VALUES ('2002-5-22')
INSERT INTO workdays VALUES ('2002-5-23')
INSERT INTO workdays VALUES ('2002-5-24')
INSERT INTO workdays VALUES ('2002-5-28')
INSERT INTO workdays VALUES ('2002-5-29')
INSERT INTO workdays VALUES ('2002-5-30')
INSERT INTO workdays VALUES ('2002-5-31')
INSERT INTO workdays VALUES ('2002-6-3')
INSERT INTO workdays VALUES ('2002-6-4')
INSERT INTO workdays VALUES ('2002-6-5')
INSERT INTO workdays VALUES ('2002-6-6')
INSERT INTO workdays VALUES ('2002-6-7')
INSERT INTO workdays VALUES ('2002-6-10')
INSERT INTO workdays VALUES ('2002-6-11')
INSERT INTO workdays VALUES ('2002-6-12')
INSERT INTO workdays VALUES ('2002-6-13')
INSERT INTO workdays VALUES ('2002-6-14')
INSERT INTO workdays VALUES ('2002-6-17')
INSERT INTO workdays VALUES ('2002-6-18')
INSERT INTO workdays VALUES ('2002-6-19')
INSERT INTO workdays VALUES ('2002-6-20')
INSERT INTO workdays VALUES ('2002-6-21')
INSERT INTO workdays VALUES ('2002-6-24')
INSERT INTO workdays VALUES ('2002-6-25')
INSERT INTO workdays VALUES ('2002-6-26')
INSERT INTO workdays VALUES ('2002-6-27')
INSERT INTO workdays VALUES ('2002-6-28')
INSERT INTO workdays VALUES ('2002-7-1')
INSERT INTO workdays VALUES ('2002-7-2')
INSERT INTO workdays VALUES ('2002-7-3')
INSERT INTO workdays VALUES ('2002-7-5')
INSERT INTO workdays VALUES ('2002-7-8')
INSERT INTO workdays VALUES ('2002-7-9')
INSERT INTO workdays VALUES ('2002-7-10')
INSERT INTO workdays VALUES ('2002-7-11')
INSERT INTO workdays VALUES ('2002-7-12')
INSERT INTO workdays VALUES ('2002-7-15')
INSERT INTO workdays VALUES ('2002-7-16')
INSERT INTO workdays VALUES ('2002-7-17')
INSERT INTO workdays VALUES ('2002-7-18')
INSERT INTO workdays VALUES ('2002-7-19')
INSERT INTO workdays VALUES ('2002-7-22')
INSERT INTO workdays VALUES ('2002-7-23')
INSERT INTO workdays VALUES ('2002-7-24')
INSERT INTO workdays VALUES ('2002-7-25')
INSERT INTO workdays VALUES ('2002-7-26')
INSERT INTO workdays VALUES ('2002-7-29')
INSERT INTO workdays VALUES ('2002-7-30')
INSERT INTO workdays VALUES ('2002-7-31')
INSERT INTO workdays VALUES ('2002-8-1')
INSERT INTO workdays VALUES ('2002-8-2')
INSERT INTO workdays VALUES ('2002-8-5')
INSERT INTO workdays VALUES ('2002-8-6')
INSERT INTO workdays VALUES ('2002-8-7')
INSERT INTO workdays VALUES ('2002-8-8')
INSERT INTO workdays VALUES ('2002-8-9')
INSERT INTO workdays VALUES ('2002-8-12')
INSERT INTO workdays VALUES ('2002-8-13')
INSERT INTO workdays VALUES ('2002-8-14')
INSERT INTO workdays VALUES ('2002-8-15')
INSERT INTO workdays VALUES ('2002-8-16')
INSERT INTO workdays VALUES ('2002-8-19')
INSERT INTO workdays VALUES ('2002-8-20')
INSERT INTO workdays VALUES ('2002-8-21')
INSERT INTO workdays VALUES ('2002-8-22')
INSERT INTO workdays VALUES ('2002-8-23')
INSERT INTO workdays VALUES ('2002-8-26')
INSERT INTO workdays VALUES ('2002-8-27')
INSERT INTO workdays VALUES ('2002-8-28')
INSERT INTO workdays VALUES ('2002-8-29')
INSERT INTO workdays VALUES ('2002-8-30')
INSERT INTO workdays VALUES ('2002-9-3')
INSERT INTO workdays VALUES ('2002-9-4')
INSERT INTO workdays VALUES ('2002-9-5')
INSERT INTO workdays VALUES ('2002-9-6')
INSERT INTO workdays VALUES ('2002-9-9')
INSERT INTO workdays VALUES ('2002-9-10')
INSERT INTO workdays VALUES ('2002-9-11')
INSERT INTO workdays VALUES ('2002-9-12')
INSERT INTO workdays VALUES ('2002-9-13')
INSERT INTO workdays VALUES ('2002-9-16')
INSERT INTO workdays VALUES ('2002-9-17')
INSERT INTO workdays VALUES ('2002-9-18')
INSERT INTO workdays VALUES ('2002-9-19')
INSERT INTO workdays VALUES ('2002-9-20')
INSERT INTO workdays VALUES ('2002-9-23')
INSERT INTO workdays VALUES ('2002-9-24')
INSERT INTO workdays VALUES ('2002-9-25')
INSERT INTO workdays VALUES ('2002-9-26')
INSERT INTO workdays VALUES ('2002-9-27')
INSERT INTO workdays VALUES ('2002-9-30')
INSERT INTO workdays VALUES ('2002-10-1')
INSERT INTO workdays VALUES ('2002-10-2')
INSERT INTO workdays VALUES ('2002-10-3')
INSERT INTO workdays VALUES ('2002-10-4')
INSERT INTO workdays VALUES ('2002-10-7')
INSERT INTO workdays VALUES ('2002-10-8')
INSERT INTO workdays VALUES ('2002-10-9')
INSERT INTO workdays VALUES ('2002-10-10')
INSERT INTO workdays VALUES ('2002-10-11')
INSERT INTO workdays VALUES ('2002-10-15')
INSERT INTO workdays VALUES ('2002-10-16')
INSERT INTO workdays VALUES ('2002-10-17')
INSERT INTO workdays VALUES ('2002-10-18')
INSERT INTO workdays VALUES ('2002-10-21')
INSERT INTO workdays VALUES ('2002-10-22')
INSERT INTO workdays VALUES ('2002-10-23')
INSERT INTO workdays VALUES ('2002-10-24')
INSERT INTO workdays VALUES ('2002-10-25')
INSERT INTO workdays VALUES ('2002-10-28')
INSERT INTO workdays VALUES ('2002-10-29')
INSERT INTO workdays VALUES ('2002-10-30')
INSERT INTO workdays VALUES ('2002-10-31')
INSERT INTO workdays VALUES ('2002-11-1')
INSERT INTO workdays VALUES ('2002-11-4')
INSERT INTO workdays VALUES ('2002-11-5')
INSERT INTO workdays VALUES ('2002-11-6')
INSERT INTO workdays VALUES ('2002-11-7')
INSERT INTO workdays VALUES ('2002-11-8')
INSERT INTO workdays VALUES ('2002-11-12')
INSERT INTO workdays VALUES ('2002-11-13')
INSERT INTO workdays VALUES ('2002-11-14')
INSERT INTO workdays VALUES ('2002-11-15')
INSERT INTO workdays VALUES ('2002-11-18')
INSERT INTO workdays VALUES ('2002-11-19')
INSERT INTO workdays VALUES ('2002-11-20')
INSERT INTO workdays VALUES ('2002-11-21')
INSERT INTO workdays VALUES ('2002-11-22')
INSERT INTO workdays VALUES ('2002-11-25')
INSERT INTO workdays VALUES ('2002-11-26')
INSERT INTO workdays VALUES ('2002-11-27')
INSERT INTO workdays VALUES ('2002-11-29')
INSERT INTO workdays VALUES ('2002-12-2')
INSERT INTO workdays VALUES ('2002-12-3')
INSERT INTO workdays VALUES ('2002-12-4')
INSERT INTO workdays VALUES ('2002-12-5')
INSERT INTO workdays VALUES ('2002-12-6')
INSERT INTO workdays VALUES ('2002-12-9')
INSERT INTO workdays VALUES ('2002-12-10')
INSERT INTO workdays VALUES ('2002-12-11')
INSERT INTO workdays VALUES ('2002-12-12')
INSERT INTO workdays VALUES ('2002-12-13')
INSERT INTO workdays VALUES ('2002-12-16')
INSERT INTO workdays VALUES ('2002-12-17')
INSERT INTO workdays VALUES ('2002-12-18')
INSERT INTO workdays VALUES ('2002-12-19')
INSERT INTO workdays VALUES ('2002-12-20')
INSERT INTO workdays VALUES ('2002-12-23')
INSERT INTO workdays VALUES ('2002-12-24')
INSERT INTO workdays VALUES ('2002-12-26')
INSERT INTO workdays VALUES ('2002-12-27')
INSERT INTO workdays VALUES ('2002-12-30')
INSERT INTO workdays VALUES ('2002-12-31')
-----Original Message-----
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 24, 2002 11:09 AM
To: CF-Talk
Subject: RE: Calculating workdays
I used something like the following to work out the number of working
days
between 2 dates. You should be able to manipulate to get what you
want:
<cfloop condition="#daysadded# LT #stagelength#">
<cfset nextdate = DateAdd("D",1,nextdate)>
<cfif DayOfWeek(nextdate) IS NOT 1 AND DayofWeek(nextdate) IS NOT 7>
<cfset daysadded = daysadded + 1>
</cfif>
</cfloop>
-----Original Message-----
From: Chris Combs [mailto:[EMAIL PROTECTED]]
Sent: 24 January 2002 17:03
To: CF-Talk
Subject: RE: Calculating workdays
What DBMS are you working with?
-----Original Message-----
From: Alex [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 24, 2002 11:50 AM
To: CF-Talk
Subject: Re: Calculating workdays
i have been looking for something like this forever and have not found
it.
Or even a way to do it without making clunking code.
On Thu, 24 Jan 2002, it-werks wrote:
> I could have sworn there was a tag on the developer's exchange, but I
sur
e
> can't find it.
>
> I am looking for a tag, formula or whatever is needed to do the
following
:
> When a number is entered into a form field named "leadtime" and a
date is
> put in a form field named "need_by" a results field will be the
calculate
d
> date that
> is the number of WEEKDAYS(mon-fri) BEFORE the Needed Date.
> In other words: I need the date to order a part which has a leadtime
of x
> number of workdays before the needby date.
>
> Sure could use some help here.
>
> Terry Troxel
>
>
______________________________________________________________________
Get Your Own Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
