counting working days

Xenobiologist · #1 (**permalink**) 06-21-11, 05:18

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?

Thanks in advance
Mega

dav1mo · #2 (**permalink**) 06-21-11, 06:58

something like?

Code:

select (date_a - date_b) - count(*)
    from holidays
where state = 'GermanFederalState'
  and holiday = 'Y'

Dave Nance

Xenobiologist · #3 (**permalink**) 06-21-11, 07:13

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

stolze · #4 (**permalink**) 06-21-11, 07:23

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).

Xenobiologist · #5 (**permalink**) 06-21-11, 08:43

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

tonkuma · #6 (**permalink**) 06-21-11, 15:39

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.

tonkuma · #7 (**permalink**) 06-21-11, 16:57

( 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
;

stolze · #8 (**permalink**) 06-23-11, 05:26

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.

Xenobiologist · #9 (**permalink**) 06-27-11, 08:14

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech