Newsgroups: comp.databases.informix Subject: Number of working days From: jparker@hpbs3645.boi.hp.com (Jack Parker) Date: 31 Aug 1995 15:23:04 -0400 Folks, What I find really annoying is deleting mail which had a name and address which I didn't bother to save anywhere. Some worthy was just worrying about number of working days between two dates in SPL. He had already done the work of stashing the Holidays into a file and doing end_date - start_date - COUNT(*) from holidays between start_date and end_date He had yet to worry about subtracting out Sundays and Saturdays, since this is something I've wanted and should properly belong in THE date library which I volunteered to write 8 years ago, I went ahead and wrote it. I did it in 4gl, so there will be some minor conversions to make it run in SPL, but it should go quietly. As always I could have taken some short cuts, but chose instead to keep the code clear. For your pleasure: --- cut here --- ######################################################## # just to test it out ######################################################## MAIN DEFINE s_date, e_date DATETIME YEAR TO DAY, w_days INTERVAL DAY(3) TO DAY PROMPT "Enter start date" for s_date # e.g. 1995-09-02 PROMPT "Enter end date" for e_date CALL work_days(s_date, e_date) RETURNING w_days DISPLAY "Work days: ", w_days END MAIN ##################################################################### # Non-inclusive number of work days between two dates # work_days(monday, tuesday) would return 1 # Jack Parker@HP, 8-31-95. ##################################################################### FUNCTION work_days(s_date, e_date) DEFINE s_date, e_date DATETIME YEAR TO DAY, offset INTEGER, offitv, wk_days, adjust INTERVAL DAY(3) TO DAY # # Problem: when start date is a sunday or saturday things get hokey, since # these AREN'T work days, lets just avoid the problem by skipping them # # Fix start date LET offset = WEEKDAY(s_date) # What IS today? IF offset = 0 THEN LET s_date = s_date + 1 UNITS DAY END IF IF offset = 6 THEN LET s_date = s_date + 2 UNITS DAY END IF # Fix end date LET offset = WEEKDAY(e_date) # What IS today? IF offset = 0 THEN LET e_date = e_date + 1 UNITS DAY END IF IF offset = 6 THEN LET e_date = e_date + 2 UNITS DAY END IF LET offset = WEEKDAY(s_date) - 1 # step back to monday LET offitv = offset USING "-##&" # convert to interval LET s_date = s_date - offitv # we are now on monday LET wk_days = e_date - s_date # number of intervening days LET adjust = (wk_days/7)*2 # number of weeks (weekends) LET wk_days = wk_days - adjust - offitv RETURN wk_days END FUNCTION --- and here --- cheers j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, BSMC Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ Back up my hard drive? You mean these things can actually run in reverse? _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ From: johnl@informix.com (Jonathan Leffler) Date: 23 Jan 1996 12:53:03 -0500 >From: bayoff@izzy.net (bayoff) >Date: Mon, 22 Jan 1996 20:40:13 >X-Informix-List-Id: > >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 =========================================================================== 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 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 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 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 #---------------------------------------------------------------------# DEFINE mths SMALLINT DEFINE mm,dd,yy SMALLINT DEFINE dte DATE # get the month and year of today LET mm = MONTH(TODAY) LET yy = YEAR(TODAY) # get the month and year of the month mths months from now WHILE mths != 0 IF mths < 0 THEN # going backward in time IF mm = 1 THEN # get December last year LET mm = 12 LET yy = yy - 1 ELSE LET mm = mm - 1 END IF LET mths = mths + 1 ELSE # going forward in time IF mm = 12 THEN # get January next year LET mm = 1 LET yy = yy + 1 ELSE LET mm = mm + 1 END IF LET mths = mths - 1 END IF END WHILE # now we need to get the month after the one we want IF mm = 12 THEN LET mm = 1 LET yy = yy + 1 ELSE LET mm = mm + 1 END IF # set dte to 1st day of the month we set up LET dte = MDY(mm,1,yy) # decrment by 1 LET dte = dte - 1 # Viola'! dte is now set to the last day of chosen month RETURN dte END FUNCTION # last_day(mths) #---------------------------------------------------------------------# FUNCTION week_days(beg_date,end_date) # Arguments: beginning and ending date # Purpose: Determine the number of week days (Mon-Fri) # for the date range (inclusive) # including an optional hook to a holiday table # Returns: SMALLINT, number of week days #---------------------------------------------------------------------# DEFINE beg_date DATE DEFINE end_date DATE DEFINE tst_date DATE DEFINE weekdays SMALLINT ## OPTIONAL: the variable below assumes a holiday table with a list ## of holidays that should not be considered weekdays #DEFINE lholi_date LIKE holiday.holi_date LET weekdays = NULL IF beg_date IS NULL OR end_date IS NULL OR end_date < beg_date THEN # this is illegal RETURN weekdays END IF LET weekdays = 0 LET tst_date = beg_date WHILE tst_date <= end_date # use the built-in WEEKDAY function to determine if this is # Monday (1) through Friday (5) IF WEEKDAY(tst_date) > 0 AND WEEKDAY(tst_date) < 6 THEN LET weekdays = weekdays + 1 END IF LET tst_date = tst_date + 1 UNITS DAY END WHILE -JL- This is also a long-winded way of doing the computation. -JL- You should be able to do it by subtraction of two dates (probably -JL- after conversion to integers), and then subtract 2/7 of the days, -JL- and compensate for the weekdays on which the end dates fall. ## OPTIONAL: if you have a holiday table, work this sort of thing in #DECLARE c_holiday CURSOR FOR # SELECT holi_date FROM holiday # WHERE holi_date BETWEEN beg_date AND end_date # #FOREACH c_holiday INTO lholi_date # # is this date is a weekday, we need to decrement # IF WEEKDAY(lholi_date) > 0 AND WEEKDAY(lholi_date) < 6 THEN # LET weekdays = weekdays - 1 # END IF #END FOREACH -JL- Not good; should be a single select statement: -JL- SELECT COUNT(*) -JL- INTO other_non_working_days -JL- FROM Holiday -JL- WHERE Holi_Date BETWEEN beg_date AND end_date -JL- AND WEEKDAY(Holi_Date) BETWEEN 1 AND 5 -JL- LET weekdays = weekdays - other_non_working_days RETURN weekdays END FUNCTION # week_days(beg_date,end_date) ======================================================================= Dennis J. Pimple dennisp@informix.com Opinions expressed Senior Consultant -------------------- are mine, and do not Informix Software Inc Voice: 303-850-0210 necessarily reflect Denver Colorado USA Fax: 303-779-4025 those of my employer. ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ From: DONALD_BOOTHBY_at_ISDLAN@IMA.ISD.STATE.IN.US (DONALD BOOTHBY) Date: 23 Jan 1996 12:58:03 -0500 Another way of doing this is via a Stored Procedure: I have included the code that I took from an I-4GL program that (I think) Jack Parker wrote. The Engine it runs on is Informix 7.11.UD1. I don't know if this code is backward compatible to earlier releases. I left in the whole thing since it works. It uses a holiday table that contains holidays that someone has to enter at some point. The holiday table cannot contain Saturday or Sunday dates. I have triggers to prevent this. The 2 dates cannot be Saturday or Sunday (or holidays) either. However, you can program around this by moving the beginning or ending dates to the nearest Monday or Friday. I think I can explain the algorithm if you want: > > 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? > Start SPL: DROP PROCEDURE k_route_info; CREATE PROCEDURE k_route_info(i_aps char(4), i_fis char (1), i_seq char(25)) RETURNING char(4), char(1), char(25), char(1), char(6), char(2), char(1), char(4), char(25), datetime year to fraction(5), datetime year to fraction(5), datetime year to fraction(5), int, int; DEFINE num_holiday INTEGER; DEFINE sw_aps_code char(4); DEFINE sw_fiscal_yr char(1); DEFINE sw_sequence_number char(25); DEFINE sw_action_code char(1); DEFINE sw_unix_id char(6); DEFINE sw_disp_type char(2); DEFINE sw_in_process char(1); DEFINE sw_dest_aps_code char(4); DEFINE sw_delivered_to char(25); DEFINE sw_receive_date datetime year to fraction(5); DEFINE sw_disp_date datetime year to fraction(5); DEFINE sw_action_date datetime year to fraction(5); DEFINE from_date DATE; DEFINE work_date DATE; DEFINE to_date DATE; DEFINE offset INTEGER; DEFINE wk_days, total_days INTEGER; DEFINE offitv INTERVAL DAY(5) TO DAY; DEFINE adjust INT; set debug file to "/users/informix/procedure.trace"; trace on; trace "begin trace."; select min(receive_date_time), max(disp_date) into from_date, to_date from status where aps_code = i_aps and fiscal_year = i_fis and sequence_number = i_seq; LET offset = WEEKDAY(from_date) - 1; LET offitv = offset UNITS DAY; LET work_date = from_date - offitv; LET total_days = to_date - work_date; LET adjust = total_days; LET adjust = (adjust / 7); LET adjust = adjust * 2; LET total_days = total_days - adjust; LET total_days = total_days - offset; SELECT count(*) INTO num_holiday FROM holiday_dates WHERE holiday > from_date AND holiday < to_date; LET total_days = total_days - num_holiday; FOREACH select aps_code, fiscal_year, sequence_number, action_code, unix_id, disp_type, in_process, dest_aps_code, delivered_to, receive_date_time, disp_date, action_date into sw_aps_code, sw_fiscal_yr, sw_sequence_number, sw_action_code, sw_unix_id, sw_disp_type, sw_in_process, sw_dest_aps_code, sw_delivered_to, sw_receive_date, sw_disp_date, sw_action_date from status where aps_code = i_aps and fiscal_year = i_fis and sequence_number = i_seq order by receive_date_time desc LET from_date = sw_receive_date; LET to_date = sw_disp_date; select count(*) into num_holiday from holiday_dates where holiday = from_date; IF num_holiday <> 0 THEN RAISE EXCEPTION -746, 0, "BEGIN DATE CONNOT BE HOLIDAY."; END IF select count(*) into num_holiday from holiday_dates where holiday = to_date; IF num_holiday <> 0 THEN RAISE EXCEPTION -746, 0, "END DATE CANNOT BE HOLIDAY."; END IF LET offset = WEEKDAY(from_date); IF offset = 0 THEN RAISE EXCEPTION -746, 0, "BEGIN DATE CANNOT BE SUNDAY."; END IF IF OFFSET = 6 THEN RAISE EXCEPTION -746, 0, "BEGIN DATE CANNOT BE SATURDAY."; END IF LET offset = WEEKDAY(to_date); IF offset = 0 THEN RAISE EXCEPTION -746, 0, "END DATE CANNOT BE SUNDAY."; END IF IF OFFSET = 6 THEN RAISE EXCEPTION -746, 0, "END DATE CANNOT BE SATURDAY."; END IF LET offset = WEEKDAY(from_date) - 1; LET offitv = offset UNITS DAY; LET work_date = from_date - offitv; LET wk_days = to_date - work_date; LET adjust = wk_days; LET adjust = (adjust / 7); LET adjust = adjust * 2; LET wk_days = wk_days - adjust; LET wk_days = wk_days - offset; SELECT count(*) INTO num_holiday FROM holiday_dates WHERE holiday > from_date AND holiday < to_date; LET wk_days = wk_days - num_holiday; RETURN sw_aps_code, sw_fiscal_yr, sw_sequence_number, sw_action_code, sw_unix_id, sw_disp_type, sw_in_process, sw_dest_aps_code, sw_delivered_to, sw_receive_date, sw_disp_date, sw_action_date, wk_days, total_days WITH RESUME; END FOREACH; trace off; END PROCEDURE with listing in '/users/informix/procedure.warnings'; end SPL donald_boothby_at_isdlan@ima.isd.state.in.us (317)232-4602 Don Boothby (DBA) State of Indiana - ISD Room N551, IGCN 100 N Senate Ave Indianapolis, IN 46204 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ From: jparker@hpbs3645.boi.hp.com (Jack Parker) Date: 25 Jan 1996 09:33:04 -0500 Since we are all posting our working day math routines. Here's my 4gl one. I pulled this out of our scheduler which has to do working day math. This function adds a number of days to a base time returning the new datetime. It handles holidays - whatever you set them to. Requires: create table holidays ( off_day date ); where off_day is any day which is a holiday ##################################################################### # working day math (add) # basetime is a moment in time (DATETIME Y TO S), # no_days is the number of days to add (INTERVAL DAY TO DAY) ##################################################################### FUNCTION work_day_add(basetime, no_days) DEFINE basetime, newtime DATETIME YEAR TO SECOND, offset, i, j INTEGER, no_days INTERVAL DAY TO DAY, cnvrt CHAR(5), int_days INTEGER LET offset = WEEKDAY(basetime) # what is offset from SUNDAY? CASE offset WHEN 6 LET basetime = basetime + 1 UNITS DAY LET offset = 0 # need to add back in later WHEN 0 LET basetime = basetime OTHERWISE LET basetime = basetime - offset UNITS DAY END CASE LET no_days = no_days + offset UNITS DAY # don't lose those days we # subtracted LET cnvrt = no_days # convert from INTERVAL... LET int_days = cnvrt # to INTEGER LET int_days = int_days/5 # how many weeks? LET int_days = (int_days * 7) + (cnvrt MOD 5) - 1 # add that many + remaindr IF cnvrt MOD 5 < 2 THEN # when TH or FR mess-up - fix LET int_days = int_days - 2 END IF LET newtime = basetime + int_days UNITS DAY # Add those days in # We now have the basic working day, but what about holidays? SELECT COUNT(*) # count how many we spanned INTO offset FROM holidays WHERE off_day BETWEEN basetime AND newtime FOR i = 1 TO offset # for that many, add one_by_one LET newtime = newtime + 1 UNITS DAY LET j = WEEKDAY(newtime) # worry about SAT, SUN is impossible IF j = 6 THEN LET newtime = newtime + 2 UNITS DAY END IF LET j = 1 WHILE j = 1 # we could be hitting another # holiday SELECT COUNT(*) INTO j FROM holidays WHERE off_day = newtime IF j = 0 THEN EXIT WHILE ELSE LET newtime = newtime + 1 UNITS DAY IF WEEKDAY(newtime) = 6 THEN # and watch for saturday LET newtime = newtime + 2 UNITS DAY END IF END IF END WHILE END FOR RETURN newtime END FUNCTION cheers j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, DMD/IS Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ "I'm with the IRS, I'm here to help you" _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++