Working Days (was sql)

>From: bayoff@izzy.net (bayoff) >Date: Mon, 22 Jan 1996 20:40:13 >X-Informix-List-Id: <news.20508> > >I am trying to write a SQL statement that will look at 2 dates and >determine the number of work days there are between them. I need to issue >the SQL against many rows. If I can at least exclude the week-ends that >would be great. The holidays, a bonus.... Does anyone have an answer? Define which holidays you mean! They vary from organization to organization within the USA (federal employees get Martin Luther King Day, most others probably don't), and they are radically different in different countries. Even within the UK, there are holidays in Scotland which aren't holidays in England. The basic calculation you seek has been discussed at various times in the past, and I enclose three possible answers. I've added some annotations to the last answer, which deals with a Holidays table. Yours, Jonathan Leffler (johnl@informix.com) #include <dislcaimer.h> =========================================================================== Date: Tue, 17 Mar 92 10:34:25 GMT From: slutsky@newjersey (Alan Slutsky) Subject: Re: Weekday Function I don't know of a function, but there is a simple algorithm you can use. Assume two variables: start_date and end_date. 1) end_date - start_date = total_number_days 2) total_number_days / 7 = number_whole_weeks (discard the remainder) 3) number_whole_weeks * 2 = number_weekend_days 4) total_number_days - number_weekend_days = number_weekdays 5) if weekday(start_date) > weekday(end_date) let number_weekdays = number_weekdays - 2 The last step is necessary to check if the remainder days spanned a weekend (i.e. start_date is a Friday and end_date is a Monday). Of course, if you also want to subtract holidays, that's another story. Alan >From richm@asterix Mon Mar 16 11:58:00 1992 >Subject: Weekday Function > >Does anyone out there have a function which calculates the >number of weekdays (ie excludes weekends) between two dates ? =========================================================================== From: walt@mathcs.emory.edu (Walt Hultgren {rmy}) Subject: Re: 4GL DateTime Variables Date: 18 Nov 91 18:25:58 GMT [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 =========================================================================== From: Dennis Pimple <dennisp@informix.com> Subject: Re: function needed Date: Thu, 6 Apr 95 9:50:23 MDT > I have a customer who is looking for a function in 4GL (or in C if not > possible in 4GL) which could extract or calculate the working days within > a quarter by passing to this function the beginning date and ending > date... By working days, I assume you mean week days (Monday-Friday). See function week_days in the code below, which is tested except for the commented optional holiday hook. I left last_day attached because you might find it useful to determine the date of end-of-quarter. ########### # INFORMIX PROFESSIONAL SERVICES ####### # ## Denver, Colorado ###### # ### ================================================== ##### # #### File: %M% SCCS: %I% %P% #### # ##### Program: aaaa.4gi ### # ###### Client: ## # ####### Author: # ########### Date: %G% %U% -JL- Not a good interface; should include reference date in argument list. -JL- In NewEra, it would be a defaulted argument. -JL- The algorithm leaves somewhat to be desired; a loop instead of some -JL- simple computations is not very sensible. #---------------------------------------------------------------------# FUNCTION last_day(mths) # Arguments: Counter +/-/0 of months # Purpose: Determine the last day of the month mths months ahead/back # eg: last_day(0) RETURNS last day this month # last_day(1) RETURNS last day next month # last_day(-12) RETURNS last day this month a year ago # Returns: DATE #-------------------------------