XQuery/Net Working Days

Contents

To calculate the "effective" age of many documents you want to count the number of working days they have been in various stages. This means you count the weekdays but not the weekend days. You can even discard the holidays if you want to have consistent aging reports.

Since NetWorkingDays is a calculation that is shared by many systems, it makes sense to use a XQuery module to put the logic into.

module namespace fxx = "http://xquery.wikibooks.org/fxx";
declare function fxx:net-working-days-n($s as xs:date, $f as xs:date, $dates as xs:date*, $total as xs:integer) as xs:integer {
if ($s= $f)
then $total
else if (fxx:weekday($s) and not($s = $dates))
then fxx:net-working-days-n($s + xs:dayTimeDuration('P1D'), $f, $dates,$total + 1)
else fxx:net-working-days-n($s + xs:dayTimeDuration('P1D'), $f, $dates,$total )
};
declare function fxx:net-working-days($s as xs:date, $f as xs:date) as xs:integer {
(: this function returns one less than the number returned from Excel NETWORKDAY :
networkdays($d,$d) should be 0 but it is 1.
networkdays and workday should be inverses and they are not
common practice seems to be to subtract one anyway.
:)
(: assumes $s <= $f :)
fxx:net-working-days-n($s,$f, (), 0)
};
declare function fxx:net-working-days($s as xs:date,$f as xs:date, $dates as xs:date*) as xs:integer {
fxx:net-working-days-n($s,$f, $dates, 0)
};

The heart of this calculation is a NetWorkingDays algorithm that is passed two dates.

The recursive function works but it is slow. It has to call itself once for each date between the two dates. An alternative approach is to count the end days in each fraction of a week, count the weeks and multiply by five.