XQuery Function To Get The Number Of Week/Work Days

I’m back. Don’t worry (my one reader), I haven’t given up on this blogging idea. I’ve just been very busy recently.

Today’s post is an XQuery function designed to get a count of the number of week (or work) days between two dates. It’s designed to mimic the Excel NETWORKDAYS function. I got the algorithm from Bernal Schooley in this thread and then adapted it to XQuery. It also makes use of the FunctX day-of-week function, so if you have FunctX functions already referenced you can take that part out.


declare namespace functx = "http://www.functx.com";

declare function functx:day-of-week
 ($date as xs:anyAtomicType?) as xs:integer? {
 if (empty($date))
 then ()
 else
  xs:integer((xs:date($date) - xs:date('1901-01-06')) div xs:dayTimeDuration('P1D')) mod 7
};

declare function local:weekdays
 ($start as xs:anyAtomicType?, $end as xs:anyAtomicType?) as xs:integer? {
 if(empty($start) or empty($end))
 then()
 else
  if($start > $end)
  then -local:weekdays($end, $start)
  else
   let $dayOfWeekStart := functx:day-of-week($start)
   let $dayOfWeekEnd := functx:day-of-week($end)
   let $adjDayOfWeekStart := if($dayOfWeekStart = 0) then 7 else $dayOfWeekStart
   let $adjDayOfWeekEnd := if($dayOfWeekEnd = 0) then 7 else $dayOfWeekEnd
   return
    if($adjDayOfWeekStart <= $adjDayOfWeekEnd)
    then xs:integer((xs:integer(days-from-duration(xs:date($end) - xs:date($start)) div 7) * 5)
     + max(((min((($adjDayOfWeekEnd + 1), 6)) - $adjDayOfWeekStart), 0)))
    else xs:integer((xs:integer(days-from-duration(xs:date($end) - xs:date($start)) div 7) * 5)
     + min((($adjDayOfWeekEnd + 6) - min(($adjDayOfWeekStart, 6)), 5)))
};

Usage:


local:weekdays('2009-06-01', '2010-06-30')

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s