473,698 Members | 2,450 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Calculating Business Days

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!
Nov 12 '05 #1
12 23780
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!


Nov 12 '05 #2
Ken
an*****@yahoo.c om (Anthony Robinson) wrote in message news:<d1******* *************** ****@posting.go ogle.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
Nov 12 '05 #3
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.goo gle.com...
an*****@yahoo.c om (Anthony Robinson) wrote in message news:<d1******* *************** ****@posting.go ogle.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

Nov 12 '05 #4
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(BUS INESS_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_COLUMN S 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(BUS INESS_NUM);
CREATE INDEX CALENDAR_YEAR ON SD_CALENDAR(SD_ YEAR, SD_MONTH);


CREATE OR REPLACE FUNCTION ADD_BUSINESS_DA YS(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.BUSINES S_NUM + DAYS_ADD = SD_TO.BUSINESS_ NUM
AND SD_TO.DAY_TYPE = 0;
RETURN(RET);
END;
/
Nov 12 '05 #5
Ken
"Anthony Robinson" <mr********@mn. rr.com> wrote in message news:<xn******* ************@tw ister.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
Nov 12 '05 #6
ak************@ yahoo.com (AK) wrote in message news:<46******* *************** ****@posting.go ogle.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(BUS INESS_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_COLUMN S 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(BUS INESS_NUM);
CREATE INDEX CALENDAR_YEAR ON SD_CALENDAR(SD_ YEAR, SD_MONTH);


CREATE OR REPLACE FUNCTION ADD_BUSINESS_DA YS(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.BUSINES S_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...
Nov 12 '05 #7
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_D ATE, 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_DAT E)
from calender c
where DAYOFWEEK(cal_d ate) 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.
Nov 12 '05 #8
AK
> >
INSERT INTO SD_CALENDAR(BUS INESS_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_DA YS(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.BUSINES S_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.
Nov 12 '05 #9
ch************* *@techie.com (Christian Maslen) wrote in message news:<b9******* *************** ****@posting.go ogle.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
Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4250
by: David Stockwell | last post by:
I''m wondering if the Calendar object has an option to only do calculations based on business days (ie M-F). Additionally does it have a way to get holidays into itself? Currently I'm considering converting my calculations over to business days and am wondering if there is anything built-in. I've tried searching but so far haven't seen such a thing built in. I'm thinking I'm just going to have to bite the bullet and create my own...
1
3729
by: igendreau | last post by:
I have users inputting a "Request Date". Upon entering a date, I need Access to populate a second field ("Due Date"). When they enter their Request Date, I want Access to set the default value of Due Date = Request Date + 9 Business Days (Holidays don't matter. Just want Saturday and Sunday taken out). Any thoughts?
7
25989
by: Sam | last post by:
Hi, I use C# in my ASP.NET projects. Here's what I need to do: I want to add x business days to a given date i.e. add 12 business days to today's date. What is the best, fastest and most efficient way for me to do this? -- Thanks, Sam
8
7512
by: =?Utf-8?B?QWw=?= | last post by:
I am working in vb2005. how can I calculate business days (not including holidays and weekends) between 2 dates? thanks Al
1
7187
by: santhoshrms | last post by:
I need to be able to determine the number of business days between two dates. Is there a function/Select Query in DB2 that can be used to do this? ...
1
2443
by: ArchMichael | last post by:
i need help again on calculating business days excluding holidays i have a field called assign date and i need to calculate 7 business days excluding holidays ( already have a table for holiday) from that date. i have read some forums on getting total business day but not the other way around
5
24539
FishVal
by: FishVal | last post by:
IMHO, the following is not a how-to-do instruction to solve a particular problem but more a concept-proof stuff demonstrating possibilities of SQL. So, let us say the problem is to calculate business days count which is defined as count of days (optionally inclusive in the current implementation) excluding weekend days and holidays. Let us say periods to calculate are stored in table associated with contacts. keyPeriodID -...
0
1437
by: josephsimonbenn | last post by:
I need to calculate the number of business days employees work in each month using vba. The problem is, some employees work 3 days, some 4 and some 5. So i want to be able to turn on/off the days via checkboxes. I found this code... which will basically count the days that aren't saturday or sunday, but Can someone modify this code so that the days are only counted if the checkboxes are selected.... Checkboxes are called: ChkSunday,...
3
9944
by: PotatoChip | last post by:
I'm working in an Access XP database and I need to create a query which calculates what the date will be 6 business days after . I have no idea where to start and most posts I find on calculating business days pertains to subtracting data in two date fields. I can build an expression in my query to add the date, Date_Due: + 5 but that doens't address the business days. Thanks in advance!
0
8675
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8604
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9160
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8897
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6521
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4619
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3050
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2331
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2002
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.