Re: 4GL DateTime Variables
Path: emory!walt
From: walt@mathcs.emory.edu (Walt Hultgren {rmy})
Newsgroups: comp.databases.informix
Message-ID: <8222@emory.mathcs.emory.edu>
Date: 18 Nov 91 18:25:58 GMT
References: <20490001@hpspkla.spk.hp.com> <1991Nov15.074048.16511@StarConn.com>
Reply-To: th@bnr.co.uk
Organization: Emory University
[Fowarded from Tony Heskett <th@bnr.co.uk> who is having problems posting. I
believe the function to which Tony refers below is weekday(). It is available
for use in both 4GL and SQL statements. WH]
------------------------------------------------------------------------------
Paul Mahler <pmahler@StarConn.com> writes -
[ ... lots of good stuff deleted ... ]
> engstrom@hpspkla.spk.hp.com (Kathleen Engstrom) writes:
> >My second question: I would like to calculate the difference in two dates
> >and then subtract out weekends and holidays from the result. I would
> >appreciate any pointers, suggestions on how to do that cleanly. A turnkey
> >application would be even better :-)
>
> As far as I know, this would require some serious coding.
> I don't know of any way to discover weekends of holidays within
> a datetime or interval.
No serious coding round here, thanks !
A few rules for what follows:
* Two date limits, between which we calculate working days.
* The date limits are *included* in the time: if the limits are 1
Jan and 5 Jan and both are workdays, they both get counted in.
* Holidays cannot be booked on weekends.
So get a table with all the holidays in it, and
SELECT COUNT(*)
INTO hol_days
FROM hols_tab
WHERE hol_date > (lower_lim - 1)
AND hol_date < (upper_lim + 1)
Then
LET work_days = upper_lim - lower_lim + 1 - hol_days
before removing weekends.
Figure out how many weeks are in the time period:
DEFINE num_weeks INTEGER
LET num_weeks = (upper_lim - lower_lim + 1) / 7
LET work_days = work_days - (num_weeks * 2)
since there are 2 weekend days per week. To check the last few days,
FOR loopdate = (lower_limit + weeks * 7) TO upper_limit
IF is_saturday(loopdate) OR is_sunday(loopdate) THEN
LET work_days = work_days - 1
END IF
END FOR
For Sat/Sun decisions (if those are your weekends, depends on
nationality), there's a standard 4GL function that gives you a
number back when called on a DATE variable.
Luckily, I've forgotten both the function-name AND the manuals,
but the idea is that DATEs that are Mondays return 1, DATEs that
are Sundays 7, or something like.
is_saturday() and is_sunday() call the informix function
and return 1 or 0 depending on the day-of-week indicated.
For the table of holidays, all you really need is a column of type
DATE, with a unique index on it for safety first and speed later.
There's no way of calculating holidays since they're arbitrary, so
some guy's going to have to type them in. You can allow that dead
easily with a shell script that runs an "isql -rf ..." form.
You may want to be able to work in half-day holidays
if you're thinking in terms of people booking time off.
Disclaimer: There may be the odd off-by-one error in the above.
PS. Hello Jim, I sent you some mail but I think it got eaten :-)
_________________________________________________________________________
Tony Heskett th@bnr.co.uk Voice: (+44) 279 429531 x 2637
BNR, London Road, Harlow, Essex, CM17 9NA Fax: (+44) 279 454187