Business Day Calculator

  • Thread starter Thread starter Karen Hart
  • Start date Start date
K

Karen Hart

I found a wonderful solution to counting business days including holidays at
www.dbforums.com/t981086.html. The solution is to create a table storing
all calendar dates, indicating which are business days and holidays, like
this:

Calendar table:
caldate wkday holiday
2004-02-01 1
2004-02-02 2
2004-02-03 3
2004-02-04 4
2004-02-05 5
2004-02-06 6
2004-02-07 7
2004-02-08 1
2004-02-09 2
2004-02-10 3
2004-02-11 4
2004-02-12 5
2004-02-13 6
2004-02-14 7
2004-02-15 1
2004-02-16 2 President's Day
2004-02-17 3

Then create a query like this:

select count (*)
from Calendar
where caldate between current_date and 'yourdate'
and wkday in (2,3,4,5,6)
and holiday is null

It works perfectly! And being that I sweat bullets when I look at
complicated code, it is perfect for me...
But I'd like to query it in a slightly different way...in my company, we
need to know what is 5 business days from today. So in the above example,
if today was 2004-02-09, 5 business days in the future would return the
result 2004-02-17.

Can anyone help? You could be my new personal hero!

Signed,
Wishin' and Hopin' in L.A. :-)
 
You asked this same question in microsoft.public.access.devtoolkits
yesterday, and I gave you an answer there.

If you feel you need to post to more than one group (HINT: it's seldom
necessary), please have the courtesy to cross-post (send the one message to
all groups at once), rather than multi-post (send individual messages to
each group). In this way, all responses to your post will be available
together, regardless of what group the responder was in, and the rest of us
won't have to read your post multiple times. (It also uses fewer server
resources)

I see you're using Outlook Express. Click the "Newsgroups:" label to the
left of the box containing the name of the current newsgroup. That will open
a dialog that will let you add additional newsgroups to your post.
 
Doug:
Thank you. I did not know the etiquitte, and I will do this in the future. I
am new to newsgroups as of the last few months, and I appreciate your help
in learning the right way to do things. :-) I had a feeling there must be
a better way, and thanks again for showing it to me!
Karen
 
Back
Top