1. Registered User
Join Date
Jun 2011
Posts
7

Hello,

I'm very new to this forum.
I 've got the following question:
How to build a UDF in DB2 (e.g. 9.7 on AIX) which is able to return the working days between two dates.

Somthing like this (pseudo code)

int countWorkingDays(fromDate, toDate, GermanFederalState)

I've got a lookup table for all the relevant holidays per state.
Plus, in Autoit(scripting language) I can calculate all the holidays.

So all I want to do is,
a) calculate the days between date a and date b
b) subtract saturdays and sundays
c) subtract all the holidays of the given state, between date a and date b
d) return the number of working days

Any idea how to achieve the goal?
Wrting an UDF in Java? SQL-PL?

Mega

2. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
something like?

Code:
```select (date_a - date_b) - count(*)
from holidays
where state = 'GermanFederalState'
and holiday = 'Y'```
Dave Nance

3. Registered User
Join Date
Jun 2011
Posts
7
Thanks, not really.
I do not have those information in a table or something like that.
I need to calculate everything in the function itself.

Days between the two dates. e.g. DateDiff
Calculating the holidays of the years from fromDate and toDate.
Subtracting sundays and saturdays and the holidays

4. Registered User
Join Date
Jan 2007
Location
Jena, Germany
Posts
2,721
You should at least have the holidays in a table (or some external file). Otherwise, it will be really hard because you'd have to know the algorithm how holidays are determined.

Enumerating all days between two dates could be done, for example, with a recursive query that starts with one date and then always adds 1 day until the end date is reached. Then you can nest that into another query, which checks that DAYOFWEEK() is not a Saturday/Sunday. Finally, subtract all holidays (from some table).

5. Registered User
Join Date
Jun 2011
Posts
7
Thanks!
There shouldn't be a problem calculating the holidays.

Autoit function to get the holidays of a year.

PHP Code:
``` Func _Feiertage(\$year)     Dim \$tmp, \$Delimiter = "|"     Dim \$HDays[16]     ;fixe Feiertage     \$HDays[0] = \$year & "/01/01" ; & \$Delimiter & "Neujahr"     \$HDays[1] = \$year & "/01/06" ; & \$Delimiter & "Heilige Drei Könige"     \$HDays[2] = \$year & "/05/01" ; & \$Delimiter & "Maifeiertag"     \$HDays[3] = \$year & "/10/03" ; & \$Delimiter & "Tag der Deutschen Einheit"     \$HDays[4] = \$year & "/10/31" ; & \$Delimiter & "Reformationstag"     \$HDays[5] = \$year & "/11/01" ; & \$Delimiter & "Allerheiligen"     \$HDays[6] = \$year & "/12/24" ; & \$Delimiter & "Heiligabend"     \$HDays[7] = \$year & "/12/25" ; & \$Delimiter & "1. Weihnachtsfeiertag"     \$HDays[8] = \$year & "/12/26" ; & \$Delimiter & "2. Weihnachtsfeiertag"     \$HDays[9] = \$year & "/12/31" ; & \$Delimiter & "Silvester"     ;variable Feiertage     \$aa = Mod(\$year, 19)     \$bb = Mod(\$year, 4)     \$cc = Mod(\$year, 7)     \$H1 = Int(\$year / 100)     \$H2 = Int(\$year / 400)     \$N = 4 + \$H1 - \$H2     \$MM = 15 + \$H1 - \$H2 - Floor(Int((8 * \$H1 + 13) / 25))     \$dd = Mod((19 * \$aa + \$MM), 30)     \$ee = Mod((2 * \$bb + 4 * \$cc + 6 * \$dd + \$N), 7)     If \$dd + \$ee = 35 Then         \$Easter = 50     Else         If \$dd = 28 And \$ee = 6 And \$aa > 10 Then             \$Easter = 49         Else             \$Easter = 22 + \$dd + \$ee         EndIf     EndIf     If \$Easter < 32 Then         \$EasterDay = \$Easter         \$EasterMonth = "03"     Else         \$EasterDay = \$Easter - 31         \$EasterMonth = "04"     EndIf     If \$EasterDay < 10 Then         \$EasterDay = "0" & \$EasterDay     EndIf     If \$year < 1900 Then ;Datumsoperationen nur mgl. wenn > 1900 , Jahr wird konvertiert         \$RestJahr = Mod(\$year, 100)         If _DateIsLeapYear(\$year) Then             If \$RestJahr < 10 Then                 \$RestJahr = "0" & \$RestJahr             EndIf             \$Tempyear = 20 & \$RestJahr         Else             If \$RestJahr < 10 Then                 \$RestJahr = "0" & \$RestJahr             EndIf             \$Tempyear = 19 & \$RestJahr         EndIf         \$EasterDate = \$Tempyear & "/" & \$EasterMonth & "/" & \$EasterDay     Else         \$EasterDate = \$year & "/" & \$EasterMonth & "/" & \$EasterDay     EndIf     \$RosDat = _DateAdd('d', -48, \$EasterDate)     \$KarDat = _DateAdd('d', -2, \$EasterDate)     \$OMoDat = _DateAdd('d', 1, \$EasterDate)     \$HiFaDat = _DateAdd('d', 39, \$EasterDate)     \$PfMoDat = _DateAdd('d', 50, \$EasterDate)     \$FroDat = _DateAdd('d', 60, \$EasterDate)     \$HDays[10] = \$RosDat ; & \$Delimiter & "Rosenmontag"     \$HDays[11] = \$KarDat ; & \$Delimiter & "Karfreitag"     \$HDays[12] = \$OMoDat ; & \$Delimiter & "Ostermontag"     \$HDays[13] = \$HiFaDat ; & \$Delimiter & "Christi Himmelfahrt"     \$HDays[14] = \$PfMoDat ; & \$Delimiter & "Pfingstmontag"     \$HDays[15] = \$FroDat ; & \$Delimiter & "Fronleichnam"     _ArraySort(\$HDays)     Return \$HDays EndFunc   ;==>_Feiertage  ```
I' ll have a look whether I get it done in Autoit completely and then port it to Java or SQL-PL

Mega

6. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Here is an example of a function which is,
a) calculate the days between date a(from_date) and date b(to_date)
b) subtract saturdays and sundays
-
d) return the number of working days

Assumptions:
1) to_date >= from_date
2) If to_date = from_date, then returns 0.
That means calculate weekdays which are larger than from_date and lower or equal to to_date.

Code:
```CREATE FUNCTION count_weekdays
(from_date DATE , to_date DATE)
RETURNS INTEGER
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
CASE
WHEN to_date >= from_date THEN
( DAYS(to_date) - DAYS(from_date) ) / 7 * 5
+ MOD( 5 + INT( 5.4 - 0.9 * DAYOFWEEK_ISO(from_date) )
- INT( 5.4 - 0.9 * DAYOFWEEK_ISO(  to_date) )
, 5 )
ELSE NULL
END
;```
where
Code:
` INT( 5.4 - 0.9 * DAYOFWEEK_ISO(from[to]_date) )`
is equivalent to
Code:
``` 5 -
CASE
WHEN DAYOFWEEK_ISO(from[to]_date) <= 5 THEN
DAYOFWEEK_ISO(from[to]_date)
ELSE 5
END```
The expressions are used to reduce the number of references
to DAYOFWEEK_ISO function which is a SYSFUN schema function.
SYSFUN schema functions are user-defined functions provided by DB2
and not the built-in functions.
Last edited by tonkuma; 06-21-11 at 16:06. Reason: Replace INT( ABS( 6 - DAYOFWEEK_ISO(from[to]_date) ) - 0.5 ) to INT( 5.4 - 0.9 * DAYOFWEEK_ISO(from[to]_date) )

7. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
( 54 - 9 * DAYOFWEEK_ISO(from[to]_date) ) / 10
may be better than
INT( 5.4 - 0.9 * DAYOFWEEK_ISO(from[to]_date) )

Because, former expression would include two conversion between int and dec and decimal operations,
while newer expression would not(but, include integer divide).

Another example:
Code:
```CREATE FUNCTION count_weekdays
(from_date DATE , to_date DATE)
RETURNS INTEGER
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
CASE
WHEN to_date >= from_date THEN
( DAYS(to_date) - DAYS(from_date) ) / 7 * 5
+ MOD( 5 + ( 54 - 9 * DAYOFWEEK_ISO(from_date) ) / 10
- ( 54 - 9 * DAYOFWEEK_ISO(  to_date) ) / 10
, 5 )
ELSE NULL
END
;```

8. Registered User
Join Date
Jan 2007
Location
Jena, Germany
Posts
2,721
I wasn't aware that you were asking specifically for German holidays. Personally, I never cared much about knowing the formula. Usually, I learn about holidays a few days before (except Christmas).

What makes things more complicated is that Fronleichnam is a holiday in Germany Bavaria/BW, but not in Thuringia (which is why I'm working today). The same applies to others like Hlg. 3 Koenige. You'd have to consider the location.

Porting the code to SQL can be done and shouldn't be hard.

9. Registered User
Join Date
Jun 2011
Posts
7
Hello @all,

thanks for your replies. It is a little bit more complex than I thought at first.
But I think, Ive got an working script in AutoIt. Now I need to convert it to a language that can be used for writing UDFs for DB2.

I'll report when it is done.

Mega

Skript and exe (no Virus inside) is included in attachment.

10. Registered User
Join Date
Oct 2012
Posts
1
Originally Posted by Xenobiologist
Hello @all,

thanks for your replies. It is a little bit more complex than I thought at first.
But I think, Ive got an working script in AutoIt. Now I need to convert it to a language that can be used for writing UDFs for DB2.

I'll report when it is done.

Mega

Skript and exe (no Virus inside) is included in attachment.
I cannot extract this file. Tryed with 7-zip, Peazip, Winrar and winzip.
Can You re-up?
I need exactly this autoit script to work on.
Thank You

11. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
The Feiertag.zip file uncompresses correctly for me using 7Zip 9.20, WinRar 3.91, and WinZip 16.5. Are you using current versions of the extractor programs?

-PatP

12. Registered User
Join Date
Jun 2011
Posts
7
Success? If not, I can post the code.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•