I tried to send this yesterday, but as far as I can tell it
never made it. Sorry if it is a duplicate.
I know this is an older post, but reading it got me thinking
about how to handle situations like holidays, businesses that want to count
Saturdays and/or Sundays as business days, and time increments that will cross
multiple weekends (the solution below of adding two based on the day of the
week makes the assumption that the increment will never cross more than one
weekend), so I wrote a stored procedure that will allow you to calculate the
nth business day after (or before by using a negative number) a given day and
take into account holidays, and allow the option of treating Saturdays and
Sundays as business days. I'll put the code below. It depends on the
existence of a holiday table (code also below) that you can load with whatever
holidays you need. With a little coding, you could even alter the table and SP
so that holidays only apply to certain regions (so that you could account for
Canada Day in Canada, but not in the US, or Evacuation day in Suffolk County,
Massachusetts, but not the rest of the world). I have also included a sample
output.
Please let me know if you have any questions.
Thanks,
Jason
Syntax: (CALL ADDBDAY (start date,number of days to skip,use holiday table,Sat
is/is not BD,Sun is/is not BD))
Sample Output:
sdate toskip usehol usesat usesun edate
---------- ---------- ---------- ---------- ---------- ----------
07/08/2013 -3 1 1 1 07/02/2013
07/09/2013 -3 1 1 1 07/03/2013
07/10/2013 -3 1 1 1 07/05/2013
07/11/2013 -3 1 1 1 07/08/2013
07/12/2013 -3 1 1 1 07/09/2013
07/25/2013 -10 1 1 1 07/11/2013
07/20/2013 -15 1 0 0 07/05/2013
07/01/2013 5 1 1 0 07/08/2013
07/03/2013 5 0 0 0 07/08/2013
07/04/2013 10 1 1 1 07/18/2013
07/03/2013 0 0 0 0 07/03/2013
07/01/2013 14 0 0 0 07/15/2013
07/02/2013 7 1 1 0 07/11/2013
Holiday table definition:
CREATE TABLE `holiday` (`holiday_id` INTEGER NOT NULL ('Value for column
holiday_id cannot be null'),`h_date` DATE NOT NULL ('Value for column h_date
cannot be null'),`h_desc` TEXT (20))
ALTER TABLE `holiday` ADD PRIMARY KEY (`holiday_id` ) ('Values for rows in
holiday must be unique','Cannot delete - values exist in another table','Cannot
update - values exist in another table')
AUTONUM `holiday_id` IN `holiday` USING 1 1 NONUM
COMMENT ON `holiday_id` IN `holiday` IS 'Uniquely identifies each holiday.'
COMMENT ON `h_date` IN `holiday` IS 'The date of the holiday.'
COMMENT ON `h_desc` IN `holiday` IS 'The name of the holiday.'
ADDDAY Stored Procedure:
-- ADDBDAY Stored Procedure
-- Jason Kramer
-- 07-16-2013
-- This RMD file is used by the ADDBDAY stored procedure. It requires
-- five parameters: the starting date, the number of days to add (can be
-- negative to calculate a date prior to the starting date),use the holiday,
-- use table, count saturday as a business day, count sunday as a business day,
-- and returns the business day that is the nth business day after (or before
-- if the 2nd paramter is negative), after the starting date.
-- This SP requires that the holiday table exist and have one DATE column named
-- h_date that contains the list of holidays. Any holiday listed in the h_date
-- column will be considered a non-business day.
-- Required parameters - vabdstart DATE - the starting date.
-- - vabdinc INTEGER - the number of BUSINESS days to
-- add or subtract.
-- - vabdholiday INTEGER - 1 (check) or 0 (do not check)
-- the holiday table.
-- - vabdsat INTEGER - 1 (Saturday is a non-business
-- day)or 0 (Saturday is a business
-- day)
-- - vabdsun INTEGER - 1 (Sunday is a non-business day)
-- or 0 (Sunday is a business day)
-- Return value - DATE - The nth business day after (or before) the start date.
SET VAR vabdretval DATE = NULL
SET VAR vabdnextday DATE = .vabdstart
SET VAR vabdadd INTEGER = NULL
SET VAR vabdbdcount INTEGER = 0
SET VAR vabdhcount INTEGER = NULL
SET VAR vabddow INTEGER = NULL
IF vabdinc = 0 THEN
SET VAR vabdretval = .vabdstart
ELSE
IF vabdinc > 0 THEN
SET VAR vabdadd = 1
ELSE
SET VAR vabdadd = -1
SET VAR vabdinc = (ABS(.vabdinc))
ENDIF
WHILE vabdbdcount < .vabdinc THEN
SET VAR vabdnextday = (ADDDAY(.vabdnextday,.vabdadd))
SET VAR vabddow = (IDWK(.vabdnextday))
IF (vabddow = 6 AND vabdsat = 0) OR (vabddow = 7 AND vabdsun = 0) OR
(vabddow < 6) THEN
IF vabdholiday = 1 THEN
SELECT COUNT h_date INTO vabdhcount FROM holiday WHERE h_date =
.vabdnextday
IF vabdhcount = 0 THEN
SET VAR vabdbdcount = .vabdbdcount + 1
ENDIF
ELSE
SET VAR vabdbdcount = .vabdbdcount + 1
ENDIF
ENDIF
ENDWHILE
SET VAR vabdretval = .vabdnextday
ENDIF
CLEAR VAR vabdstart,vabdinc,vabdholiday,vabdsat,vabdsun,vabdnextday,vabdadd
CLEAR VAR vabdbdcount,vabdhcount,vabddow
RETURN .vabdretval
Jason Kramer
University Archives and Records Management
002 Pearson Hall
(302) 831 - 3127 (voice)
(302) 831 - 6903 (fax)
From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On
Behalf Of Alastair Burr
Sent: Monday, July 08, 2013 1:28 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: calculating dates
Even though I had seen that you had a solution I thought that there could be a
way with the ADDDAY and DNW functions but I failed!
It sounds to me like a good enhancement request, however, ADDBusinessDays along
the lines of ADDDAY with the same negative usage to go backwards.
Just a thought,
Regards,
Alastair.
From: Dan Goldberg<mailto:[email protected]>
Sent: Monday, July 08, 2013 3:50 PM
To: RBASE-L Mailing List<mailto:[email protected]>
Subject: [RBASE-L] - Re: calculating dates
Nvm, I have figured a way.
In the report I used some functions and placed them into variables. I would get
the day of the week. If the day of the week is less than 4 then subtract 5
instead of 3.
INTEGER : D : vdow = (idwk(onlinedate))
INTEGER : D : vdaysub = (iflt(.vdow,4,5,3))
DATE : D : vreqdate = (onlinedate - .vdaysub)
Dan Goldberg
From: Dan Goldberg<mailto:[email protected]>
Sent: Monday, July 08, 2013 7:20 AM
To: RBASE-L Mailing List<mailto:[email protected]>
Subject: [RBASE-L] - calculating dates
I need to display a date in a view/report that is three business days previous
to another date. I can easily do (columndate - 3) but I need to omit saturday
and sunday from the date calculation.
7/8/2013/Monday - 3 days would be 7/3/2013
7/9/2013/Tuesday - 3 days would be 7/4/2013
7/10/2013/Wednesday - 3 days would be 7/5/2013
7/11/2013/Thursday - 3 days would be 7/8/2013
I can write a routine to calculate at each row, one by one, to calculate them
but I was hoping that there is an easy way to accomplish this. If anyone has
any ideas please let me know.
TIA
Dan Goldberg
