Tags: aware, business, calculating, column, database, date, db2, defined, function, instance, mysql, oracle, sql, system, tablecalled, user, willcalculate

Calculating Business Days

On Database » DB2

18,669 words with 12 Comments; publish: Sun, 30 Dec 2007 23:45:00 GMT; (25062.50, « »)

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!

All Comments

Leave a comment...

  • 12 Comments
    • Sounds like a job for writing your own usder-defined functions.

      This article might be a place to start:

      http://www-106.ibm.com/developerwor...p/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!

      #1; Sun, 30 Dec 2007 23:47:00 GMT
    • ansonee.db2.todaysummary.com.yahoo.com (Anthony Robinson) wrote in message news:<d18e85f8.0401211052.4a5627ff.db2.todaysummary.com.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

      #2; Sun, 30 Dec 2007 23:48:00 GMT
    • 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" <kenfar42.db2.todaysummary.com.yahoo.com> wrote in message

      news:ea65eb54.0401211434.7567c3d0.db2.todaysummary.com.posting.google.c om...

      > ansonee.db2.todaysummary.com.yahoo.com (Anthony Robinson) wrote in message

      news:<d18e85f8.0401211052.4a5627ff.db2.todaysummary.com.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

      #3; Sun, 30 Dec 2007 23:49:00 GMT
    • 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;

      /

      #4; Sun, 30 Dec 2007 23:50:00 GMT
    • "Anthony Robinson" <mrobinson7.db2.todaysummary.com.mn.rr.com> wrote in message news:<xnEPb.97554$fq1.27661.db2.todaysummary.com.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

      #5; Sun, 30 Dec 2007 23:51:00 GMT
    • ak_tiredofspam.db2.todaysummary.com.yahoo.com (AK) wrote in message news:<46e627da.0401211813.25277750.db2.todaysummary.com.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...

      #6; Sun, 30 Dec 2007 23:52:00 GMT
    • 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.

      #7; Sun, 30 Dec 2007 23:53:00 GMT
    • > > 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.

      #8; Sun, 30 Dec 2007 23:54:00 GMT
    • christian.maslen.db2.todaysummary.com.techie.com (Christian Maslen) wrote in message news:<b9c8cfba.0401221343.5d4f239f.db2.todaysummary.com.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 lissianski.db2.todaysummary.com.mail.ru

      Best regards,

      Konstantin

      #9; Sun, 30 Dec 2007 23:55:00 GMT
    • 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?

      #10; Sun, 30 Dec 2007 23:56:00 GMT
    • 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.

      #11; Sun, 30 Dec 2007 23:57:00 GMT
    • 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

      #12; Sun, 30 Dec 2007 23:58:00 GMT