Calculating Business Days

[Anthony Robinson]

Is anyone aware of a function (system or user defined) that will
calculate business days? For instance: I have a column in as table
called DATE. I want to be able to add five business days to that date
and come up with a new date. Is that possible.

Also, is there anyway that DB2 can be aware of holidays? Maybe load
them onto the server in some type of reference file or something.

I ask these questions because I'm working on a banking application and
these are two MAJOR hurdles for us to get over.

Any help would be appreciated.

Thanks!

[Blair Adamache]

Sounds like a job for writing your own usder-defined functions.
This article might be a place to start:

http://www-106.ibm.com/developerwork.../0211yip3.html

Anthony Robinson wrote:

Is anyone aware of a function (system or user defined) that will
calculate business days? For instance: I have a column in as table
called DATE. I want to be able to add five business days to that date
and come up with a new date. Is that possible.

Also, is there anyway that DB2 can be aware of holidays? Maybe load
them onto the server in some type of reference file or something.

I ask these questions because I'm working on a banking application and
these are two MAJOR hurdles for us to get over.

Any help would be appreciated.

Thanks!

[Ken]

an*****@yahoo.com (Anthony Robinson) wrote in message news:<d1**************************@posting.google. com>...
<snip>

I ask these questions because I'm working on a banking application and
these are two MAJOR hurdles for us to get over.

You could create a time dimension table. These are commonly used in
star-schemas in data marts, but also provide some value in oltp
situations like you're describing. The time dimension would be faster
and easier to maintain than a procedure that needed to calculate
"first saturday of the month", etc.

A typical implementation would involve a primary key of date, with
non-key attributes describing the date. In a warehouse you'd be more
likely to use a surrogate key than date (or timestamp, etc), but
that's just an implementation detail. You could then also write stored
procedures that would access this table if you want to encapsulate it
behind an API.

ken

[Anthony Robinson]

Ken:

Not to be a mooch, but could you give me an idea of how this would be
implemented? 've worked with date dimensions before, but am having a hard
time grasping the consept you're referring to.

Any further insight would be greatly appreciated.

thanks!

"Ken" <ke******@yahoo.com> wrote in message
news:ea**************************@posting.google.c om...

an*****@yahoo.com (Anthony Robinson) wrote in message news:<d1**************************@posting.google. com>... <snip>

I ask these questions because I'm working on a banking application and
these are two MAJOR hurdles for us to get over.

You could create a time dimension table. These are commonly used in
star-schemas in data marts, but also provide some value in oltp
situations like you're describing. The time dimension would be faster
and easier to maintain than a procedure that needed to calculate
"first saturday of the month", etc.

A typical implementation would involve a primary key of date, with
non-key attributes describing the date. In a warehouse you'd be more
likely to use a surrogate key than date (or timestamp, etc), but
that's just an implementation detail. You could then also write stored
procedures that would access this table if you want to encapsulate it
behind an API.

ken

[AK]

Here is Oracle PL/SQl script that accomplishes just that
It's just as easy in DB2
replace ROWNUM with ROW_NUMBER() and some oither functions such as TRUNC

DROP TABLE SD_CALENDAR;
CREATE TABLE SD_CALENDAR(
BUSINESS_NUM NUMBER(5) NOT NULL,
NUM NUMBER(5) NOT NULL,
SD_DAY DATE NOT NULL,
DAY_OF_WEEK NUMBER(1) NOT NULL,--0 - Sun,..., 6 - Sat
DAY_TYPE NUMBER(2) NOT NULL,
SD_YEAR NUMBER(4) NOT NULL,
SD_MONTH NUMBER(2) NOT NULL
);

INSERT INTO SD_CALENDAR(BUSINESS_NUM, NUM, SD_DAY, DAY_OF_WEEK, DAY_TYPE,
SD_YEAR, SD_MONTH)
SELECT 0 AS BUSINESS_NUM,
N AS NUM,
(TO_DATE('09/01/2003', 'MM/DD/YYYY') + N) AS SD_DAY,
MOD(N+1, 7) AS DAY_OF_WEEK,
CASE MOD(N+1, 7)
WHEN 0 THEN 1
WHEN 6 THEN 1
ELSE 0
END AS DAY_TYPE,
0 AS SD_YEAR,
0 AS SD_MONTH
FROM
(select (rownum-1) AS N from user_tAB_COLUMNS WHERE ROWNUM<1000) SEQ;
COMMIT;

--- UPDATE HERE HOLIDAY SCHEDULE FOR 2003 / 2004 ---------------

UPDATE SD_CALENDAR
SET BUSINESS_NUM = (SELECT COUNT(*) FROM SD_CALENDAR S1 WHERE S1.NUM <
SD_CALENDAR.NUM AND S1.DAY_TYPE=0),
SD_YEAR = EXTRACT(YEAR FROM SD_DAY),
SD_MONTH = EXTRACT(MONTH FROM SD_DAY);
COMMIT;

CREATE INDEX CALENDAR_DAY ON SD_CALENDAR(SD_DAY);
CREATE INDEX CALENDAR_NUM ON SD_CALENDAR(BUSINESS_NUM);
CREATE INDEX CALENDAR_YEAR ON SD_CALENDAR(SD_YEAR, SD_MONTH);


CREATE OR REPLACE FUNCTION ADD_BUSINESS_DAYS(DATE_FROM IN DATE, DAYS_ADD IN
NUMBER)
RETURN DATE
IS RET DATE;
BEGIN
SELECT SD_TO.SD_DAY
INTO RET
FROM
SD_CALENDAR SD_FROM,
SD_CALENDAR SD_TO
WHERE SD_FROM.SD_DAY = TRUNC(DATE_FROM, 'DDD')
AND SD_FROM.BUSINESS_NUM + DAYS_ADD = SD_TO.BUSINESS_NUM
AND SD_TO.DAY_TYPE = 0;
RETURN(RET);
END;
/

[Ken]

"Anthony Robinson" <mr********@mn.rr.com> wrote in message news:<xn*******************@twister.rdc-kc.rr.com>...

Not to be a mooch, but could you give me an idea of how this would be
implemented?

No problem at all. AK has probably already answered this for you, but
in case you aren't familiar with PL/SQL...here's a little more info.
A typical time dimension table will look something like this:

CREATE TABLE md_time_to_day (
time_id INTEGER NOT NULL, # 1 = 2004/01/01
through 1460 = 2008/01/01
date DATE NOT NULL,
year INTEGER NOT NULL, # 2004, etc
month_of_year INTEGER NOT NULL, # 1-12
day_of_month INTEGER NOT NULL, # 1-31
quarter INTEGER NOT NULL, # 1-4
week_of_year INTEGER NOT NULL, # 1-52
day_of_week VARCHAR(10) NOT NULL, # 'monday', etc
season VARCHAR(10) NOT NULL, # 'summer', etc
part_of_week CHAR(03) NOT NULL, # 'day' or 'end'
federal_holiday CHAR(03) NOT NULL, # 'yes' or 'no'
state_holiday CHAR(03) NOT NULL, # 'yes' or 'no'
company_holiday CHAR(03) NOT NULL # 'yes' or 'no'
) IN ts_dim
;
ALTER TABLE md_time_to_day ADD CONSTRAINT md_time_to_day_pk PRIMARY
KEY (time_id);
ALTER TABLE md_time_to_day ADD CONSTRAINT md_time_to_day_x2 UNIQUE
(date);

Now, you can populate a table like this from a pl/sql script like AK
provided, a script (python, perl, etc), etc.

Ken

[Anthony Robinson]

ak************@yahoo.com (AK) wrote in message news:<46**************************@posting.google. com>...

Here is Oracle PL/SQl script that accomplishes just that
It's just as easy in DB2
replace ROWNUM with ROW_NUMBER() and some oither functions such as TRUNC

DROP TABLE SD_CALENDAR;
CREATE TABLE SD_CALENDAR(
BUSINESS_NUM NUMBER(5) NOT NULL,
NUM NUMBER(5) NOT NULL,
SD_DAY DATE NOT NULL,
DAY_OF_WEEK NUMBER(1) NOT NULL,--0 - Sun,..., 6 - Sat
DAY_TYPE NUMBER(2) NOT NULL,
SD_YEAR NUMBER(4) NOT NULL,
SD_MONTH NUMBER(2) NOT NULL
);

INSERT INTO SD_CALENDAR(BUSINESS_NUM, NUM, SD_DAY, DAY_OF_WEEK, DAY_TYPE,
SD_YEAR, SD_MONTH)
SELECT 0 AS BUSINESS_NUM,
N AS NUM,
(TO_DATE('09/01/2003', 'MM/DD/YYYY') + N) AS SD_DAY,
MOD(N+1, 7) AS DAY_OF_WEEK,
CASE MOD(N+1, 7)
WHEN 0 THEN 1
WHEN 6 THEN 1
ELSE 0
END AS DAY_TYPE,
0 AS SD_YEAR,
0 AS SD_MONTH
FROM
(select (rownum-1) AS N from user_tAB_COLUMNS WHERE ROWNUM<1000) SEQ;
COMMIT;

--- UPDATE HERE HOLIDAY SCHEDULE FOR 2003 / 2004 ---------------

UPDATE SD_CALENDAR
SET BUSINESS_NUM = (SELECT COUNT(*) FROM SD_CALENDAR S1 WHERE S1.NUM <
SD_CALENDAR.NUM AND S1.DAY_TYPE=0),
SD_YEAR = EXTRACT(YEAR FROM SD_DAY),
SD_MONTH = EXTRACT(MONTH FROM SD_DAY);
COMMIT;

CREATE INDEX CALENDAR_DAY ON SD_CALENDAR(SD_DAY);
CREATE INDEX CALENDAR_NUM ON SD_CALENDAR(BUSINESS_NUM);
CREATE INDEX CALENDAR_YEAR ON SD_CALENDAR(SD_YEAR, SD_MONTH);


CREATE OR REPLACE FUNCTION ADD_BUSINESS_DAYS(DATE_FROM IN DATE, DAYS_ADD IN
NUMBER)
RETURN DATE
IS RET DATE;
BEGIN
SELECT SD_TO.SD_DAY
INTO RET
FROM
SD_CALENDAR SD_FROM,
SD_CALENDAR SD_TO
WHERE SD_FROM.SD_DAY = TRUNC(DATE_FROM, 'DDD')
AND SD_FROM.BUSINESS_NUM + DAYS_ADD = SD_TO.BUSINESS_NUM
AND SD_TO.DAY_TYPE = 0;
RETURN(RET);
END;
/

Thanks - I'll set to work on decoding this for DB2. One question: what
is the DB2 equivalent of TO_DATE (or what does TO_DATE do in Oracle),
and why do you set it to 09/01/2003?

How does this scenario perform in a production environment? Are there
some drawbacks with having to use a table to do the calculations? How
is concurrency? We'll be having anywhere from 100 to 10,000 users at
some point and want to mke sure that whatever solution I implement
will be able to handle the load.

Thanks for all the help...

[Christian Maslen]

Hi Anthony,

Is anyone aware of a function (system or user defined) that will
calculate business days? For instance: I have a column in as table
called DATE. I want to be able to add five business days to that date
and come up with a new date. Is that possible.

As has been posted, you need to roll your own. It's easy enough to
maintain your own holidays table. From this you can generate your own
business days table and then calculate the next business day any
number of days into the future. Below is how I'd do it...

-- Table to store public holidays. Store as much info on the holidays
-- as you like. I've always only needed the description.
CREATE TABLE HOLIDAYS (
HOLIDAY_DATE DATE NOT NULL
, H0LIDAY_DESC VARCHAR(25) NOT NULL WITH DEFAULT
, CONSTRAINT PK_HOL PRIMARY KEY (HOLIDAY_DATE)
);

-- insert some holidays
INSERT INTO HOLIDAYS (
HOLIDAY_DATE
, H0LIDAY_DESC
)
VALUES ('2004-01-01', 'New Years Day')
,('2004-01-26', 'Australia Day')
,('2004-12-25', 'Christmas Day')
,('2004-12-26', 'Boxing Day')
,('2004-12-27', 'Christmas Day Holiday')
,('2004-12-28', 'Boxing Day Holiday');
-- Create a table function to return the business days in a year
-- excluding weekends. You can make the range anything relative
-- to the supplied date. I've gone with a year in advance.
CREATE FUNCTION BUSINESS_DATES (START_DATE DATE)
RETURNS TABLE ( BUS_DAY_SEQ INTEGER
, CAL_DATE DATE
, DAY_NAME VARCHAR(15))
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
with calender(BASE_DATE, CAL_DATE)
as(
VALUES (START_DATE, START_DATE)
union all
select BASE_DATE
, cal_date + 1 day
from calender
where cal_date + 1 DAYS < BASE_DATE + 1 YEAR
)
select ROW_NUMBER() OVER() AS BUS_DAY_SEQ
, CAL_DATE
, DAYNAME(CAL_DATE)
from calender c
where DAYOFWEEK(cal_date) not in (1,7)
AND NOT EXISTS (SELECT *
FROM HOLIDAYS H
WHERE H.HOLIDAY_DATE = C.CAL_DATE);

-- Test the table function.
SELECT *
FROM TABLE (BUSINESS_DATES (CURRENT DATE) ) T;

-- Create a scalar function to get the next business day
-- n days from the supplied date.
CREATE FUNCTION NEXT_BUS_DATE (CURR_BUS_DATE DATE
, DAYS_FROM_DATE INTEGER)
RETURNS DATE
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT C3.CAL_DATE
FROM (
SELECT C.BUS_DAY_SEQ
,C.CAL_DATE
FROM TABLE (BUSINESS_DATES (CURR_BUS_DATE) ) C
,(SELECT MIN(CAL_DATE) AS CAL_DATE
FROM TABLE (BUSINESS_DATES (CURR_BUS_DATE) ) T
WHERE CAL_DATE >= CURR_BUS_DATE
) T1
WHERE C.CAL_DATE = T1.CAL_DATE
) C2
, TABLE (BUSINESS_DATES (CURR_BUS_DATE) ) C3
WHERE C2.BUS_DAY_SEQ + DAYS_FROM_DATE = C3.BUS_DAY_SEQ;
-- Test the use of the scalar function
values (NEXT_BUS_DATE (DATE('2004-01-23'), 5));

Christian.

[AK]

> >

INSERT INTO SD_CALENDAR(BUSINESS_NUM, NUM, SD_DAY, DAY_OF_WEEK, DAY_TYPE,
this statement populates the calendar table with thousands of rows. We
ran in just once.
SD_YEAR, SD_MONTH)
SELECT 0 AS BUSINESS_NUM,
N AS NUM,
(TO_DATE('09/01/2003', 'MM/DD/YYYY') + N) AS SD_DAY,
Now the table contains several thousand consecutive dates starting
from September 2003.
I needed dates starting from '09/01/2003'. In DB2 use DATE function.
Refer to Paul Yip's article on DB2DD (Fun with Dates)

CREATE OR REPLACE FUNCTION ADD_BUSINESS_DAYS(DATE_FROM IN DATE, DAYS_ADD IN
this function performs very fast, it is a self join on an indexed
column
NUMBER)
RETURN DATE
IS RET DATE;
BEGIN
SELECT SD_TO.SD_DAY
INTO RET
FROM
SD_CALENDAR SD_FROM,
SD_CALENDAR SD_TO
WHERE SD_FROM.SD_DAY = TRUNC(DATE_FROM, 'DDD')
AND SD_FROM.BUSINESS_NUM + DAYS_ADD = SD_TO.BUSINESS_NUM
AND SD_TO.DAY_TYPE = 0;
RETURN(RET);
END;
/

How does this scenario perform in a production environment? Are there
some drawbacks with having to use a table to do the calculations? How
is concurrency? We'll be having anywhere from 100 to 10,000 users at
some point and want to mke sure that whatever solution I implement
will be able to handle the load.

I've successfully used calendar tables in DB2. Performance is usually
very good, because queries with calendar tables are usually simpler,
and simpler queries have better chances to get good execution plans

I've posted an Oracle script, because it accomplishes exactly what you
need.
It's used in a production database with 25+ terabytes of data and
1,500+ users
Performs very well.

[Konstantin Lissianski]

ch**************@techie.com (Christian Maslen) wrote in message news:<b9**************************@posting.google. com>...

Hi Anthony,

Is anyone aware of a function (system or user defined) that will
calculate business days? For instance: I have a column in as table
called DATE. I want to be able to add five business days to that date
and come up with a new date. Is that possible.

Hi!

Although it is not an exact answer to your question, but if you are
interested, I have a tool which does much dirty work for you and
creates a very powerfull time dimension structure for you (more than
200 attributes are currently available). It supports generating
various calendar related attributes like the number of days to the
next holiday, the number of holiday days in a period and so on.
If you are interested I can send you a more complete description.
Write me at li********@mail.ru

Best regards,
Konstantin

[AK]

Christian,

I think your approach runs slower than mine if you need to add, for
instance, 10 business days. Also your approach would work 10 times
slower when adding 100 business days, because it menas 10 times more
iterations.
Also I think your approach uses up significantly more CPU, which is
precious because processors are pricey and IBM charges by the
processor too.

What do you think?

[Christian Maslen]

Hi AK,

I think your approach runs slower than mine if you need to add, for
instance, 10 business days. Also your approach would work 10 times
slower when adding 100 business days, because it menas 10 times more
iterations.

I used a generalised calender function (always returns a year of
days), you can add a day count parameter to the calendar function so
it wouldn't waste iterations.

I'd say there is a good chance a permanent calendar table and a
function built on top would run faster than a generated calendar, but
you never know - unless you have the time to test it out (Serge would
have a better idea of the performance than me). I just thought it was
worth presenting an alternative approach.

Christian.

[AK]

Hi Christian,

I'd say there is a good chance a permanent calendar table and a
function built on top would run faster than a generated calendar, but
you never know - unless you have the time to test it out (Serge would
have a better idea of the performance than me). I just thought it was
worth presenting an alternative approach.

I was doing comparisons like that 2 years ago with 7.1, at that time
recursion was consistently MUCH slower and it was using more CPU.
Since then, a lot could have changed, but I haven't re-evaluated that
old decision to avoid recursion whenever possible

One more thing: a query with a simple SQL UDF like the one I posted
might be re-written by the optimizer and get a much better plan. I
think queries with recursion cannot be rewritten.

Please correct me if I'm wrong