Generate a list of pay periods (two per month) between two dates, using XQuery
xquery version "3.1"; | |
import module namespace functx="http://www.functx.com"; | |
(: Calculate the number of months between two dates, rounding down :) | |
declare function local:months-between-dates-floor($start-date as xs:date, $end-date as xs:date) { | |
local:months-between-dates-floor($start-date, $end-date, xs:yearMonthDuration("P0M")) | |
}; | |
(: A helper function for local:months-between-dates-floor :) | |
declare %private function local:months-between-dates-floor($start-date as xs:date, $end-date as xs:date, $months as xs:yearMonthDuration) { | |
if ($start-date + xs:yearMonthDuration("P1M") gt $end-date) then | |
$months | |
else | |
local:months-between-dates-floor($start-date + xs:yearMonthDuration("P1M"), $end-date, $months + xs:yearMonthDuration("P1M")) | |
}; | |
(: Generate a list of pay periods, two per month, between two dates :) | |
declare function local:pay-periods-between-dates($start as xs:string, $end as xs:string) { | |
let $start-date := $start => xs:date() | |
let $end-date := $end => xs:date() | |
let $months := local:months-between-dates-floor($start-date, $end-date) | |
return | |
array { | |
for $m in 1 to months-from-duration($months) + 1 | |
let $months-offset := xs:yearMonthDuration("P" || $m || "M") - xs:yearMonthDuration("P1M") | |
let $period1-start := $start-date + $months-offset | |
let $period1-end := $start-date + $months-offset + xs:dayTimeDuration("P14D") | |
let $period2-start := $period1-end + xs:dayTimeDuration("P1D") | |
let $period2-end := functx:date($period1-start => year-from-date(), $period1-start => month-from-date(), $period1-start => functx:days-in-month()) | |
return | |
( | |
if ($period1-end le $end-date) then | |
map { | |
"period": ($m - 1) * 2 + 1, | |
"start": $period1-start, | |
"end": $period1-end | |
} | |
else | |
(), | |
if ($period2-end le $end-date) then | |
map { | |
"period": ($m - 1) * 2 + 2, | |
"start": $period2-start, | |
"end": $period2-end | |
} | |
else | |
() | |
) | |
} | |
}; | |
local:pay-periods-between-dates("2020-01-01", "2020-12-31") |
[ | |
{ | |
"period": 1, | |
"start": "2020-01-01", | |
"end": "2020-01-15" | |
}, | |
{ | |
"period": 2, | |
"start": "2020-01-16", | |
"end": "2020-01-31" | |
}, | |
{ | |
"period": 3, | |
"start": "2020-02-01", | |
"end": "2020-02-15" | |
}, | |
{ | |
"period": 4, | |
"start": "2020-02-16", | |
"end": "2020-02-29" | |
}, | |
{ | |
"period": 5, | |
"start": "2020-03-01", | |
"end": "2020-03-15" | |
}, | |
{ | |
"period": 6, | |
"start": "2020-03-16", | |
"end": "2020-03-31" | |
}, | |
{ | |
"period": 7, | |
"start": "2020-04-01", | |
"end": "2020-04-15" | |
}, | |
{ | |
"period": 8, | |
"start": "2020-04-16", | |
"end": "2020-04-30" | |
}, | |
{ | |
"period": 9, | |
"start": "2020-05-01", | |
"end": "2020-05-15" | |
}, | |
{ | |
"period": 10, | |
"start": "2020-05-16", | |
"end": "2020-05-31" | |
}, | |
{ | |
"period": 11, | |
"start": "2020-06-01", | |
"end": "2020-06-15" | |
}, | |
{ | |
"period": 12, | |
"start": "2020-06-16", | |
"end": "2020-06-30" | |
}, | |
{ | |
"period": 13, | |
"start": "2020-07-01", | |
"end": "2020-07-15" | |
}, | |
{ | |
"period": 14, | |
"start": "2020-07-16", | |
"end": "2020-07-31" | |
}, | |
{ | |
"period": 15, | |
"start": "2020-08-01", | |
"end": "2020-08-15" | |
}, | |
{ | |
"period": 16, | |
"start": "2020-08-16", | |
"end": "2020-08-31" | |
}, | |
{ | |
"period": 17, | |
"start": "2020-09-01", | |
"end": "2020-09-15" | |
}, | |
{ | |
"period": 18, | |
"start": "2020-09-16", | |
"end": "2020-09-30" | |
}, | |
{ | |
"period": 19, | |
"start": "2020-10-01", | |
"end": "2020-10-15" | |
}, | |
{ | |
"period": 20, | |
"start": "2020-10-16", | |
"end": "2020-10-31" | |
}, | |
{ | |
"period": 21, | |
"start": "2020-11-01", | |
"end": "2020-11-15" | |
}, | |
{ | |
"period": 22, | |
"start": "2020-11-16", | |
"end": "2020-11-30" | |
}, | |
{ | |
"period": 23, | |
"start": "2020-12-01", | |
"end": "2020-12-15" | |
}, | |
{ | |
"period": 24, | |
"start": "2020-12-16", | |
"end": "2020-12-31" | |
} | |
] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment