How to group consecutive dates in XSLT?

Question

I have an xml file (sample below) and I want to group this xml based on consecutive Time_Off_Date.

<Root>
  <Entry>
    <Employee_ID>101</Employee_ID>
    <Time_Off_Details>
      <Time_Off_Date>2017-12-01</Time_Off_Date>
    </Time_Off_Details>
    <Time_Off_Details>
      <Time_Off_Date>2017-12-02</Time_Off_Date>
    </Time_Off_Details>
    <Time_Off_Details>
      <Time_Off_Date>2017-12-04</Time_Off_Date>
    </Time_Off_Details>
    <Time_Off_Details>
      <Time_Off_Date>2017-12-05</Time_Off_Date>
    </Time_Off_Details> 
  </Entry>
  <Entry>
    <Employee_ID>102</Employee_ID>
    <Time_Off_Details>
      <Time_Off_Date>2017-12-10</Time_Off_Date>
    </Time_Off_Details>
    <Time_Off_Details>
      <Time_Off_Date>2017-12-13</Time_Off_Date>
    </Time_Off_Details>
    <Time_Off_Details>
      <Time_Off_Date>2017-12-14</Time_Off_Date>
    </Time_Off_Details>
  </Entry>
</Root>

The final output should look like this (in CSV format).

Employee ID   Time Off Start  Time Off End
101           12/1/2017       12/2/2017
101           12/4/2017       12/5/2017
102           12/10/2017      12/10/2017
102           12/13/2017      12/14/2017

Is there a way to achieve this using XSLT 2.0 and without using recursive functions?? I am new to XSLT so any advice is appreciated.

Why would you use the exact same tag for the start and end dates? Why not have one `` and in there `` and ``?! And of course your Employee 102 has an odd number of dates and we have to guess that the 10th is a duplicate... — Alexis Wilke, Feb 11 '18 at 23:15
If you just want to treat dates in odd positions as start dates and those in even positions as end dates, that's easy and doesn't really need grouping. But where you have three dates and decide arbitrarily that one of them is both a start and an end date, I don't know what logic you are applying. — Michael Kay, Feb 12 '18 at 06:40
Is the logic that the input XML only holds individual days off (so one day at a time), and you want to group these individual days where they happen to be consecutive? — Tim C, Feb 12 '18 at 08:48
@AlexisWilke This is how Workday (the system I am getting the xml data from) is creating the xml file. Would it be easier to if the xml file had different tags?? — Ankita, Feb 12 '18 at 13:38

score 2 · Answer 1 · answered Feb 12 '18 at 10:10

If the logic is that the input XML only holds individual days off and you want to group these individual days where they happen to be consecutive, then you can use xsl:for-each-group to select the Time_Off_Details with the group-starting-with set to the elements where the Time_Off_Date is not consecutive with the previous element.

Try this XSLT

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                xmlns:xs="http://www.w3.org/2001/XMLSchema"
                version="2.0">

  <xsl:output method="text" />
  <xsl:strip-space elements="*" />

  <xsl:template match="Entry">
    <xsl:for-each-group select="Time_Off_Details" 
                        group-starting-with="*[not(xs:date(Time_Off_Date) = xs:date(preceding-sibling::*[1]/Time_Off_Date) + xs:dayTimeDuration('P1D'))]">
        <xsl:value-of select="../Employee_ID" />
        <xsl:text>,</xsl:text>
        <xsl:value-of select="Time_Off_Date" />
        <xsl:text>,</xsl:text>
        <xsl:value-of select="current-group()[last()]/Time_Off_Date" />
        <xsl:text>&#10;</xsl:text>
    </xsl:for-each-group>
  </xsl:template>
</xsl:stylesheet>

Thanks so much for the solution. This worked like a charm! – Ankita Feb 12 '18 at 14:46 — Ankita, Feb 12 '18 at 14:46

score 1 · Answer 2 · answered Feb 12 '18 at 10:05

This can be nicely expressed in XQuery 3 using the tumbling window clause (https://www.w3.org/TR/xquery-31/#id-tumbling-windows):

for $entry in Root/Entry
for tumbling window $date in $entry//Time_Off_Date/xs:date(.)
start $s when true()
end $e next $n when $n - $e gt xs:dayTimeDuration('P1D')
return string-join(($entry/Employee_ID, $date[1], $date[last()]), '&#9;')

http://xqueryfiddle.liberty-development.net/6qM2e25

As XSLT 2 processors like Saxon 9 or XmlPrime also support XQuery this might be an alternative to using XSLT.

For XSLT you might need to explain why you don't want to use a recursive function.

Hi Martin, The product I am working on, they do not allow me to use recursive functions so I`ll have to find out alternate solutions. — Ankita, Feb 12 '18 at 13:49

score 0 · Answer 3 · answered Feb 12 '18 at 10:51

Your task can be done in XSLT 2.0 using for-each-group.

First you have to sort all Time_Off_Date elements by their full content.

Each group starts with a Time_Off_Date element, for which it does not exist any other Time_Off_Date element with the content equal to the previous date, compared to the current date.

To compute the previous date, as a string, the following sequence is needed:

Take the current date.
Subtract the period of 1 day.
Format it as yyyy-mm-dd.

Then, for each group you need:

Read the date from the first group member.
Read the date from the last group member.
Print Employee_ID and both dates, formatted as you require.

So the whole script can look like below:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:transform version="2.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xsl:output method="text"/>

  <xsl:template match="Root">
    <xsl:text>Employee ID,Time Off Start,Time Off End&#xA;</xsl:text>
      <xsl:for-each-group select="Entry/Time_Off_Details/Time_Off_Date"
        group-starting-with=".[not(//Entry/Time_Off_Details/Time_Off_Date[. =
          format-date(xs:date(current()) - xs:dayTimeDuration('P1D'),
          '[Y0001]-[M01]-[D01]')])]">
        <xsl:sort select="."/>
        <xsl:variable name="startDate" select="current-group()[1]"/>
        <xsl:variable name="lastDate" select="current-group()[last()]"/>
        <xsl:value-of select="../../Employee_ID"/>
        <xsl:text>,</xsl:text>
        <xsl:value-of select="format-date($startDate,'[M01]/[D1]/[Y0001]')"/>
        <xsl:text>,</xsl:text>
        <xsl:value-of select="format-date($lastDate,'[M01]/[D1]/[Y0001]')"/>
        <xsl:text>&#xA;</xsl:text>
      </xsl:for-each-group>
  </xsl:template>
</xsl:transform>

Thanks so much for the solution! Appreciate your help. – Ankita Feb 12 '18 at 14:47 — Ankita, Feb 12 '18 at 14:47

How to group consecutive dates in XSLT?

3 Answers3

Linked