Calculate business date

[MarcLodge]

Hi All,
This subject has been touched on before but I don't think the answers given in thread178-841031 or thread178-520462 help me much. Also, page 391 of Graeme Birchall's cookbook version 9 gives a different flavour (

http://mysite.verizon.net/Graeme_Birchall/cookbook/DB2V91CK.PDF)

but I don't think I can use that either.

What I have is a date and an integer. The integer is a number of business/working days that a user is allowed before they are forced to do something. What I need to do is to store the enforcement date on the row. The integer is more than likely going to be a number less than 7, but I have to design something that will cater for a number larger than that.

Thoughts?

Marc

 

[SkipVought]

Hi,

This is just a suggestion. In my industry, we have a table of Manufacturing Dates (MDATE), that skip weekends and company holidays. The diffeence between any two MDATES is the number of WORKING DAYS.

Check with your DB Admin.

Skip,
[sub]
[red][/red]
[/sub]

 

[MarcLodge]

Skip,

At my site we don't have an MDATE table as suggested. I guess I could look into setting one up but still not sure how I could use this to get the answer I want.

To recap, I want to pass to SQL:
A date (let's use today's date for arguments sake) 21-02-2007 (Wednesday)
A number of days eg 4
and have returned to me next Tuesday's date (27-02-2007) ie. 4 working days after the date passed.

I'm looking at messing about with the function DAYOFWEEK or something along those lines, but am struggling for inspiration at the moment.

 

[ddiamond]

Marc,

I think whatever approach you take, you will need some sort of look up table because holidays may vary from company to company. You could create a holiday table that lists all holidays and weekends. You could then write a stored procedure or stored function that starts with your initial date and then counts up, skipping any dates found in your holiday table. If I understand Skip's approach, it sounds a bit cleaner, although it does require a rather large lookup table. The idea is that the MDate table would map all work dates to a numeric index like the following:

21-02-2007 -> 3
22-02-2007 -> 4
23-02-2007 -> 5
26-02-2007 -> 6
27-02-2007 -> 7

Notice that the WorkDate skips from the 23rd to the 26th, but the DateIndex is continuous.
With a table setup like that, you could right a query something like:

select a.WorkDate
from MDate a
where a.DateIndex = (
  select b.DateIndex+4 from MDate b
  where b.WorkDate = '22-02-2007')

 

[blom0344]

If you need to work in other dates other than weekends, you really need a special calendertable.
If you only need to accomodate for weekends, then it is doable with combination of CASE and MOD function.

The MOD functionality can be used to cater for values over 7 days , cause adding (example) 6,13,20,27 will not matter for the actual correction.

If I calculate this through then you have a scenario with:

1. position in the week (3 distinct ones)
2. at the most 7 sets of increments

This indicates you can build a CASE statement (like in a UDF) that tackles the problem in no more than 3*7 = 21 subcases.

Hope I am not to vague, but I am at home without any test opportunity

Ties Blom

 

[SkipVought]

It is EXACTLY what you need.

For instance I have a function named...
[tt]
DateConv(Conversion_Code As String, The_Date As String)
[/tt]
I can convert two cleandar dates to MDAYS, the difference being the number of working days between the two dates.

Or I can convert MDAY to calendar day.
[tt]
CalDate M_Date
2/21/2007 10182 'wed
2/22/2007 10183 'thu
2/23/2007 10184 'fri
2/24/2007 10184 'sat
2/25/2007 10184 'sun
2/26/2007 10185 'mon
[/tt]

MsgBox DateConversion("CM",#2/26/2007#) - DateConversion("CM",#2/23/2007#)    'one day

Skip,
[sub]
[red][/red]
[/sub]

 

[MarcLodge]

Dan,

This has potential, but I'm still hunting for a solution that does not involve setting up a new table.

I am happy, at this moment in time, to just try and ignore the weekends, thereby making the parameters of the problem slightly easier.

What I'm now looking to do is create, on the fly, a table that contains one row for each date between current date and current date + numbwer of working days. eg if the start date is today and the number of working days is four:
21/02/2007
22/02/2007
23/02/2007
24/02/2007

For some reason I can't use WITH DATE_TAB AS (SELECT etc. as it doesn't seem to work for me. I'm on a Z/OS 390 mainframe and am using QMF.

If anybody has any clues, all info gratefully received.....

 

[LKBrwnDBA]

In Oracle we use this function, maybe you could convert it to DB2:

CREATE OR REPLACE Function add_business_days
   (start_date_in date, days_in number)
   return date
IS
   v_counter number;
   v_sign number;
   v_new_date date;
   v_day_number number;
BEGIN
   v_counter := 1;
   v_sign := SIGN(days_in);
   v_new_date := start_date_in;
   while v_counter <= ABS(days_in)
   loop
      v_new_date := v_new_date + v_sign;
      v_day_number := TO_CHAR(v_new_date, 'D');
      if v_day_number not in (1,7) then
         v_counter := v_counter + 1;
      end if;
   end loop;
RETURN v_new_date;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLERRM);
END;
/

PS: The meaning of the Oracle functions used is:

SIGN(<number>) = Returns 1 if <number> is positive, 0 if <number> is zero and -1 if <number> is negative.

ABS(<number>) = Returns the absolute value of <number>.

TO_CHAR(<date>, 'D')= Returns the number of the day (1-7) starting Sunday (1) to Saturday (7).

Good luck!


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[MarcLodge]

I've created a table called T_DATES with one column DATE_COL with 4 rows 21/02/2007, 22/02/2007, 23/02/2007, 24/02/2007. The following SQL gives me the answer I require:

SELECT CURRENT_DATE
+ 4 DAYS
+ NOOFSATSUNS DAYS
FROM
(SELECT COUNT(*) AS NOOFSATSUNS
FROM T_DATES
WHERE DAYOFWEEK_ISO(DATE_COL) IN (6,7)) AS TEMP_TAB

The challenge, as I see it, is to now dynamically create the T_DATES table from the parameter given ie. if the number of working days is 5, then the table should contain 5 rows.

 

[ddiamond]

Skip,

What is DateConv? The top part of your post appears to be blank. Is some of it missing?

- Dan

 

[SkipVought]

DateConversion is a function I wrote for converting between calendar date and manufacturing date in our system. It's just a handy way of getting a value, for a given set of parameters, from a database like the one I suggested to the OP.

Skip,
[sub]
[red][/red]
[/sub]

 

[sathyarams]

Marc, Try this one

This will list all working days ... So, if you want the n-th working day, add a where clause to the final select ..

counts days only from the next day .. If you want today to be the first working day, change

select 0,current date,'N' from sysibm.sysdummy1

to

select 1,current date,'Y' from sysibm.sysdummy1

In this case, I assume 'today' will always be a working day

with holstab(holiday) as 
(
values(date('2007-03-05')),
       (date('2007-03-13')),
       (date('2007-06-10'))
)
,
 temp(rowcount1,tdate,valid_row) as 
(
select 0,current date,'N' from sysibm.sysdummy1 
union all
select case when dayofweek(tdate+1 day) = 1 or dayofweek(tdate+1 day) = 7 or ((tdate+1 day) in (select holiday from holstab))
            then rowcount1 else rowcount1+1 end , 
       tdate+1 day,
       case when dayofweek(tdate+1 day) = 1 or dayofweek(tdate+1 day) = 7 or ((tdate+1 day) in (select holiday from holstab) )
            then 'N'  else 'Y' end 
from temp where rowcount1< 10 
       
)

select * from temp where valid_row='Y'

rowcount1<10 is the portion that has to be changed to get the number of days ..

I have used a temp table holstab, but in real-life it will be a normal table.

HTH

Sathyaram



For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[sathyarams]

Sorry Marc, I didn't realize you were on ZOS ...

This will work only from V8 of DB2 ZOS

Cheers
Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp