XQuery/Net Working Days
Motivation
editTo 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.
Approach
editSince 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.
Sample Test Driver
editxquery version "1.0";
import module namespace fxx = "http://xquery.wikibook.org/fxx" at "net-working-days.xq";
(: Test driver for Net Working Days
: tags to generate documentation using xqdoc.org scripts at http://www.xqdoc.org/qs_exist.html
:
: @return XHTML table for next "caldays" days from now including working days calculations from today
: @input-parameter: caldays - an integer number of calendar days in the future from now
:
:)
let $cal-days := xs:integer(request:get-parameter("caldays", "30"))
let $now := xs:date(substring(current-date(),1,10))
return
<html>
<body>
<h1>Days from {$now}</h1>
<p>Today is a {fxx:day-of-week-name-en(xs:date(substring(current-date(),1,10)))}</p>
<p>Format: net-working-days.xq?cal-days=50</p>
<table border="1">
<thead>
<tr>
<th>Cal Days</th>
<th>Future Date</th>
<th>Day of Week</th>
<th>Net Working Days</th>
</tr>
</thead>
{
for $i in (0 to $cal-days)
let $d := $now + xs:dayTimeDuration(concat('P',$i,'D'))
let $dow := fxx:day-of-week($d)
return
<tr>
<td align="center">{$i}</td>
<td align="center">{$d}</td>
<td align="center">{fxx:day-of-week-name-en(xs:date(substring($d,1,10)))}</td>
<td align="center">{fxx:net-working-days(xs:date(substring(current-date(),1,10)),$d)}</td>
</tr>
}
</table>
<br/>
<a href="index.xhtml">Back to Unit Testing Main Menu</a>
<br/>
<a href="../../index.xhtml">Back to CRV Main Menu</a>
</body>
</html>
Discussion
editThe 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.
Code??
Acknowledgments
editAn initial version of this was provided by Chris Wallace.