Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2011
    Posts
    7

    Question Unanswered: counting working days

    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

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    something like?

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

  3. #3
    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. #4
    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).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    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($year19)
        
    $bb Mod($year4)
        
    $cc Mod($year7)
        
    $H1 Int($year 100)
        
    $H2 Int($year 400)
        
    $N $H1 $H2
        $MM 
    15 $H1 $H2 Floor(Int(($H1 13) / 25))
        
    $dd Mod((19 $aa $MM), 30)
        
    $ee Mod(($bb $cc $dd $N), 7)
        If 
    $dd $ee 35 Then
            $Easter 
    50
        
    Else
            If 
    $dd 28 And $ee 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 mglwenn 1900 Jahr wird konvertiert
            $RestJahr 
    Mod($year100)
            If 
    _DateIsLeapYear($yearThen
                
    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. #6
    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 17: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. #7
    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. #8
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    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.
    Attached Files Attached Files

  10. #10
    Join Date
    Oct 2012
    Posts
    1
    Quote Originally Posted by Xenobiologist View Post
    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. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    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
  •