IIUG Software Repository Files

Subject: Calculate working days using stored procedure
From: Jonathan Leffler <jleffler@informix.com>
Date: Mon, 29 Jun 1998 09:23:07 -0700 (PDT)

On Fri, 26 Jun 1998, Nils Myklebust wrote:
> This is a slightly difficult problem with many solutions.  [...]

> On Wed, 24 Jun 1998 22:36:22 GMT, sjsyau@my-dejanews.com wrote:
> >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.

On 15th May 1998, under the subject 'Re: ESQLC and date/datetime routines',
I posted some skeletal (and bug laden) ESQL/C code to add N business days
to a given date.  As far as I recall, I also posted a follow up on 18th May
with a fully tested version of the code, but Dejanews denies any knowledge
of this.  I also thought I sent it to the IIUG, but that site seems to deny
it too.  I must be hallucinating.

Anyway, here is a pair of ESQL/C routines which add N business days to a
given date, where N can be positive or negative.

	typedef long Date;

	/* Declaration should be in a header for use elsewhere! */
	extern Date AddNBusinessDays(Date indate, int ndays);
	extern Date AddNBusinessDaysHolidays(Date indate, int ndays);
	extern int HolidaysBetween(Date d1, Date d2);

As distributed, the routines return the same answer.  There are hooks for
handling holidays in AddNBusinessDaysHolidays() using the dummy function
HolidaysBetween() which takes to dates and should return the number of
holidays (as opposed to weekend days) between the two specified dates (the
range includes those dates).  The dummy version simply returns 0; if you
want to record real holidays, rewrite the function to work the way you
think it should.

Obviously, this isn't a direct answer to the question -- it is ESQL/C code
and not SPL code.  However, there is nothing in it that cannot be converted
into SPL.  I'm not certain that it is the tightest possible algorithm;
there may be other fancy tricks which compare start and end day-of-week
values and give you a better way of handling the weekends.  However, there
is test code which allows you to see that this produces what I regard as
the correct answer.

Remember that if your business works 6 days a week (like many retail
businesses), then the rules have to be changed.  Also, if your business
takes Sunday and Monday off (again, this could be a shop), the rules change
again.

Yours,
Jonathan Leffler (jleffler@informix.com) #include <witticism.h>
Guardian of DBD::Informix -- see http://www.perl.com/CPAN