Subject: Number of working days
Newsgroups: comp.databases.informix

From: [email-masked] (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
[email-masked]
_____________________________________________________________________________

  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: [email-masked] (Jonathan Leffler)
Date: 23 Jan 1996 12:53:03 -0500

>From: [email-masked] (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 ([email-masked]) #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: [email-masked] (Walt Hultgren {rmy})
Subject: Re: 4GL DateTime Variables
Date: 18 Nov 91 18:25:58 GMT

[Fowarded from Tony Heskett <[email-masked]> 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 <[email-masked]> writes -
    [ ... lots of good stuff deleted ... ]
> [email-masked] (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                 [email-masked]  Voice: (+44) 279 429531 x 2637
BNR, London Road, Harlow, Essex, CM17 9NA  Fax:   (+44) 279 454187

===========================================================================

From: Dennis Pimple <[email-masked]>
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         [email-masked]    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: [email-masked] (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

[email-masked]     (317)232-4602
Don Boothby (DBA)      State of Indiana - ISD
Room N551, IGCN        100 N Senate Ave Indianapolis, IN 46204

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

From: [email-masked] (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
[email-masked]
_____________________________________________________________________________

	         "I'm with the IRS, I'm here to help you"
_____________________________________________________________________________
   Any opinions expressed herein are my own and not those of my employers.
_____________________________________________________________________________

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++