CODEWITHSUNDEEP
xquerymarklogicmarklogic-8xquery-3.0
mpuram
mpuram

Reputation: 149

Quarterly Dates Calculation [XQuery]

I am trying to generate a report which fetches quarterly report i.e. based on startDate and endDate. How to calculate startDate and endDate of any quarter if I have quarter name as Q1 and year as parameters in XQuery or Marklogic.

Example: if I have Quarter=q1 and Year=2018 as parameters, then I need to get startDate as 01-01-2018 and endDate as 31-03-2018

Upvotes: 1

Views: 215

Answers (1)

Mads Hansen
Mads Hansen

Reputation: 66806

You could use functx:last-day-of-month() and other standard date functions to construct the start and end date for the given year and quarter.

The example code below will return a sequence of xs:date objects with the first being the start date, and the second the end date.

xquery version "1.0-ml";
import module namespace functx = "http://www.functx.com" 
  at "/MarkLogic/functx/functx-1.0-doc-2007-01.xqy";

declare function local:quarter-range($year as xs:integer, $quarter as xs:integer) {
  let $month := 3 * $quarter
  let $end :=  xs:date($year || "-"|| substring(string(100 + $month), 2)||"-01")
  let $start-date := $end - xs:yearMonthDuration("P2M")
  let $end-date := functx:last-day-of-month($end)
  return
     ($start-date, $end-date)
};

local:quarter-range(2018, 2)

You could enhance that to instead construct and return a cts:and-query() with cts:element-range-query:

xquery version "1.0-ml";
import module namespace functx = "http://www.functx.com" 
  at "/MarkLogic/functx/functx-1.0-doc-2007-01.xqy";

declare function local:quarter-range(
    $element as xs:QName, 
    $year as xs:integer, 
    $quarter as xs:integer) 
as cts:query 
{
  let $month := 3 * $quarter
  let $end :=  xs:date($year || "-"|| substring(string(100 + $month), 2)||"-01")
  let $start-date := $end - xs:yearMonthDuration("P2M")
  let $end-date := functx:last-day-of-month($end)
  return
     cts:and-query((
       cts:element-range-query($element, ">=", $start-date), 
       cts:element-range-query($element, "<=", $end-date)
     ))
};

local:quarter-range(xs:QName("myDateElement"), 2018, 2)

Upvotes: 3

Related Questions