Entering the holidays of a few centuries is a lot of typing.. ;-) Kind regards, Geert
Van: general-boun...@developer.marklogic.com [mailto:general-boun...@developer.marklogic.com] Namens Damon Feldman Verzonden: vrijdag 17 juni 2011 15:28 Aan: General MarkLogic Developer Discussion Onderwerp: Re: [MarkLogic Dev General] Net Working Days calculation? Since you only need the count of holidays in some date range in order to subtract it, store the holidays somewhwere and count them up. I imagine the total holidays for a period of a few hundred years will only be a few thousand dates. This could be stored in a sequence as a server-field. Linearly scanning through 1000 dates for particular values should take milliseconds. Or storing the dates as a document and using a range index may help if the sequence is too long. To optimize further, a map:map with known-holiday-count-before and known-holiday-count-after for every date would allow fastest calcuation, I think. Damon ________________________________ From: general-boun...@developer.marklogic.com [general-boun...@developer.marklogic.com] On Behalf Of Geert Josten [geert.jos...@daidalos.nl] Sent: Friday, June 17, 2011 8:06 AM To: General MarkLogic Developer Discussion Subject: Re: [MarkLogic Dev General] Net Working Days calculation? Hi Jason, That was my initial thought as well. Such calculation would scale much better. Here the more scalable approach: import module namespace functx = "http://www.functx.com"; at "/MarkLogic/functx/functx-1.0-nodoc-2007-01.xqy"; let $weekdays := ("su", "mo", "tu", "we", "th", "fr", "sa") let $holidays := (xs:date("2011-06-13")) (: second easter day:) let $start := xs:date("2011-06-09") let $end := xs:date("2011-06-15") let $start := xs:date("2011-06-06") let $end := xs:date("2011-06-13") let $startday := functx:day-of-week($start) let $endday := functx:day-of-week($end) let $days := days-from-duration($end - $start) + 1 let $whole-weeks := floor($days div 7) let $extra-days := $days mod 7 let $extra-saterday := if ($startday + $extra-days - 1 > 5) then 1 else 0 let $extra-sunday := if ($startday + $extra-days - 1 > 6) then 1 else 0 return ( "start:", $startday, $weekdays[$startday + 1], "end:", $endday, $weekdays[$endday + 1], "days:", $days, "whole weeks:", $whole-weeks, "extra-days:", $extra-days, "extra-saterday:", $extra-saterday, "extra-sunday:", $extra-sunday, "working days:", $days - 2 * $whole-weeks - $extra-saterday - $extra-sunday) But this really calls for a way to calculate the holidays, which would be useful for my earlier suggestion as well. Unfortunately, not all holidays are as predictable.. Kind regards, Geert Van: general-boun...@developer.marklogic.com [mailto:general-boun...@developer.marklogic.com] Namens Jason Hunter Verzonden: vrijdag 17 juni 2011 1:39 Aan: General MarkLogic Developer Discussion Onderwerp: Re: [MarkLogic Dev General] Net Working Days calculation? If you don't care about holidays, it's pretty easy to do it efficiently. Just count the days and do a little edge checking to decide how many were weekends. You don't even have to consider leap year logic because that's included in the day count. Just don't let the time span cross year 1582. ;) There's other oddities too. For example, from http://en.wikipedia.org/wiki/Gregorian_calendar: "In Alaska, the change took place when Friday, 6 October 1867 was followed again by Friday, 18 October after the US purchase of Alaska from Russia, which was still on the Julian calendar. Instead of 12 days, only 11 were skipped, and the day of the week was repeated on successive days, because the International Date Line was shifted from Alaska's eastern to western boundary along with the change to the Gregorian calendar." Good luck. ;) -jh- On Jun 16, 2011, at 4:04 PM, seme...@hotmail.com<mailto:seme...@hotmail.com> wrote: I guess the only way is to loop through all the dates and check their day of week. I was hoping there was something slicker, but it doesn't look like it. Thanks Geert. that's some fine code there. -Ryan ________________________________ From: geert.jos...@daidalos.nl<mailto:geert.jos...@daidalos.nl> To: general@developer.marklogic.com<mailto:general@developer.marklogic.com> Date: Thu, 16 Jun 2011 08:36:04 +0200 Subject: Re: [MarkLogic Dev General] Net Working Days calculation? Hi Ryan, Fun question! Hadn't done it yet, but that doesn't matter. How about this? It returns a list of all workable days. Wrap in a count to get a number. Perhaps not the fastest/smartest solution, but it should get you going.. import module namespace functx = "http://www.functx.com"; at "/MarkLogic/functx/functx-1.0-nodoc-2007-01.xqy"; let $holidays := (xs:date("2011-06-13")) (: second easter day:) let $start := xs:date("2011-06-09") let $end := xs:date("2011-06-16") return for $day in (0 to days-from-duration($end - $start)) let $date := $start + xs:dayTimeDuration(concat("P",$day,"D")) let $weekday := functx:day-of-week($date) let $is-weekend := ($weekday = (0, 6)) let $is-holiday := exists(index-of($date, $holidays)) where not($is-weekend) and not($is-holiday) return $date Kind regards, Geert Van: general-boun...@developer.marklogic.com<mailto:general-boun...@developer.marklogic.com> [mailto:general-boun...@developer.marklogic.com] Namens seme...@hotmail.com<mailto:seme...@hotmail.com> Verzonden: donderdag 16 juni 2011 0:32 Aan: general@developer.marklogic.com<mailto:general@developer.marklogic.com> Onderwerp: [MarkLogic Dev General] Net Working Days calculation? Has anyone done a Net Working Days calculation in XQuery? This would be the number of business days between a start and end date. Holiday inclusion would be awesome. -Ryan _______________________________________________ General mailing list General@developer.marklogic.com<mailto:General@developer.marklogic.com> http://developer.marklogic.com/mailman/listinfo/general _______________________________________________ General mailing list General@developer.marklogic.com<mailto:General@developer.marklogic.com> http://developer.marklogic.com/mailman/listinfo/general
_______________________________________________ General mailing list General@developer.marklogic.com http://developer.marklogic.com/mailman/listinfo/general
