Re: Calculate working days using stored procedure
Nils Myklebust wrote:
>
> This is a slightly difficult problem with many solutions. Here is one
> possible (I haven't tested this so check everything for yourself):
>
Actually it doesn't work too well.
The problem is that you are going to create a huge database of normal
days.
The other problem is defining what a *Holiday* is.
You will need to create a *holiday* table.
I suggest that you have a date field, and a country code as a minimum.
For example, the US celibrates the 4th of July. France celibrates the
14th of July.
Also if the *holiday* falls on a weekend, the holiday may be taken on
July 3rd, or July 5th.
Let us also keep in mind that some *holidays* are still work days.
What will work, yet will not be as efficient as a +10, would be a while
loop or some other
looping structure.
int day_count;
DATE start_date, curr_date;
curr_date = start_date;
while(day_count >0) {
if ( Is_weekday(curr_date) ){
if (Is_holiday(curr_date) == 0) {
day_count--;
}
}
cur_date++;
}
Where:
DATE is the datatype for DATE;
And Is_weekday checks to see that the day of week is not 0 or 7
(Going from memory of the Informix function to return the day of week)
Is_holiday is the function that checks the database.
Implementation is left as an exercise for the student.
Caveats:
While this may be slower than a simple arithmatic function, it does
allow for the
flexibility for the program/application to define what they consider a
working day.
Most US companies have 7 paid vacation days which can be set on a
whim. Some companies
choose 6 holidays, then have a floating 7th day. Germany has 21
national holidays.
Now unless Informix has changed their ways, small tables. (Under 2000
rows do a sequential
scan rather than use the index.) So you want to maintain a smaller
table. Besides its easier
to maintain a list of holidays, rather than a list of work days.
HTH
-Uncle Mikey
> If you create a table thus:
>
> create table calendar(
> caldate date,
> workdayno integer);>
> primary key caldate
> index on workdayno
>
> and insert at least the dates that are working days with workdayno
> starting on 1 for the first date that is a workday and increased by 1
> for every following date that is also a workday, you can do as follows
> for your particular calculation:
>
> select caldate from calendar
> where workdayno = (select workdayno + 10 from calendar where caldate =
> mdy(6,27,1998))>
> For a more general calendar I would probably put in a few more fields
> like a marker for hollidays and possibly others.
> This table will not contain many rows even if you need dates for 10 or
> 20 years.
>
> On Wed, 24 Jun 1998 22:36:22 GMT, sjsyau@my-dejanews.com wrote:
>
> >Please help me here.
> >
> >Given:
> >
> >Today's date + Working Days
> >Today's date - Working Days
> >
> >I need to find out the result in date format. The working days means to
> >exclude weekends and holidays.
> >
> >Ex,
> > 06-27-1998 + 10
> >should = 07-10-1998
> >
--
#include <std_disclaimer.h> /* Mike Segel (MS385) */
#include <No_Spam.h>
#ifdef OFFENDED_BY_CONTENT
The author takes no responsibility for this post.
Any resembalence to a coherent rational thought is purely coincidence.
-The Management.
#endif
*****************************
* Attention *
-*- Due to AGIS's Refusal to Act Responsibly
-*- Due to ACSI's latest actions, they have been pardoned. [E.Spire]
We are blocking all of their domains at the packet level.
This block will exist until they modify their policies to
conform to existing RFCs and net community standards.
We encourage all ISPs and domain holders to do the same.
*****************************