Group by consecutive dates and sum by similar dates in xslt

Question

Gurus I am try to print data rows by consecutive dates and taking a sum of all the time off units in each row into by one summarized row with start date being the first day of the time off and end date being the last time off in the consecutive group. I used below xslt and it worked excellently however there could be case when a time off is cancelled in the system then for the same date I can get a negative value for the unit in which case my xslt below is not working as I am unable to handle similar dates and similar type scenarios. Any advise please.

XML

<?xml version='1.0' encoding='UTF-8'?>
<Data>
    <Worker>
        <Worker_ID>12</Worker_ID>
        <Time_Off>
            <Type>Compassionate Leave</Type>
            <Date>2018-02-09-08:00</Date>
            <Units>1</Units>
        </Time_Off>
        <Time_Off>
            <Type>Compassionate Leave</Type>
            <Date>2018-02-08-08:00</Date>
            <Units>1</Units>
        </Time_Off>
        <Time_Off>
            <Type>Compassionate Leave</Type>
            <Date>2018-02-08-08:00</Date>
            <Units>-1</Units>
        </Time_Off>
        <Time_Off>
            <Type>Compassionate Leave</Type>
            <Date>2018-02-01-08:00</Date>
            <Units>1</Units>
        </Time_Off>
        <Time_Off>
            <Type>Statutory Holiday</Type>
            <Date>2018-02-07-08:00</Date>
            <Units>1</Units>
        </Time_Off>
        <Time_Off>
            <Type>Statutory Holiday</Type>
            <Date>2018-02-06-08:00</Date>
            <Units>1</Units>
        </Time_Off>
    </Worker>

</Data>

Xslt:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:mf="http://example.com/mf"
    exclude-result-prefixes="xs mf"
    version="3.0">

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

    <xsl:function name="mf:date" as="xs:date">
        <xsl:param name="input" as="xs:string"/>
        <xsl:sequence select="xs:date(substring($input, 1, 10))"/>
    </xsl:function>

    <xsl:function name="mf:line" as="xs:string">
        <xsl:param name="group" as="element(Time_Off)*"/>
        <xsl:value-of 
            select="$group[1]/../Worker_ID, 
            $group[1]/Type, 
            mf:date($group[1]/Date),
            mf:date($group[last()]/Date),
            sum($group/Units)"
            separator=","/>      
    </xsl:function>

    <xsl:template match="Worker">
        <xsl:for-each-group select="Time_Off" group-by="Type">
            <xsl:variable name="sorted-times" as="element(Time_Off)*">
                <xsl:perform-sort select="current-group()">
                    <xsl:sort select="mf:date(Date)"/>
                </xsl:perform-sort>
            </xsl:variable>
            <xsl:for-each-group select="$sorted-times" group-by="mf:date(Date) - xs:dayTimeDuration('P1D') * position()">
                <xsl:value-of select="mf:line(current-group()) || '&#10;'"/>
            </xsl:for-each-group>
        </xsl:for-each-group>
    </xsl:template>

</xsl:stylesheet>

The output I get is below:

12,Compassionate Leave,2018-02-01,2018-02-01,1
12,Compassionate Leave,2018-02-08,2018-02-08,1
12,Compassionate Leave,2018-02-08,2018-02-09,0
12,Statutory Holiday,2018-02-06,2018-02-07,2

My desired output is below:

12,Compassionate Leave,2018-02-01,2018-02-01,1
12,Compassionate Leave,2018-02-08,2018-02-09,1
12,Statutory Holiday,2018-02-06,2018-02-07,2

Martin Honnen Martin Honnen · Accepted Answer · 2018-02-25T21:06:08

You have posted an XSLT 3 stylesheet so I guess although you tagged the question as XSLT 2 it is fine to use XSLT 3, therefore here is a suggestion using xsl:iterate:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:mf="http://example.com/mf"
    exclude-result-prefixes="xs mf"
    version="3.0">

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

  <xsl:function name="mf:date" as="xs:date">
      <xsl:param name="input" as="xs:string"/>
      <xsl:sequence select="xs:date(substring($input, 1, 10))"/>
  </xsl:function>

  <xsl:function name="mf:line" as="xs:string">
      <xsl:param name="group" as="element(Time_Off)*"/>
      <xsl:value-of 
        select="$group[1]/../Worker_ID, 
                $group[1]/Type, 
                mf:date($group[1]/Date),
                mf:date($group[last()]/Date),
                sum($group/Units)"
                separator=","/>      
  </xsl:function>

  <xsl:template match="Worker">
      <xsl:for-each-group select="Time_Off" group-by="Type">
          <xsl:variable name="sorted-times" as="element(Time_Off)*">
              <xsl:perform-sort select="current-group()">
                  <xsl:sort select="mf:date(Date)"/>
              </xsl:perform-sort>
          </xsl:variable>
          <xsl:iterate select="$sorted-times">
              <xsl:param name="group" as="element(Time_Off)*" select="()"/>
              <xsl:on-completion>
                 <xsl:value-of select="mf:line($group), ''" separator="&#10;"/> 
              </xsl:on-completion>
              <xsl:variable name="new-group" as="xs:boolean"
                select="$group 
                   and mf:date(Date) - mf:date($group[last()]/Date) gt xs:dayTimeDuration('P1D')"/>
              <xsl:if test="$new-group">
                  <xsl:value-of select="mf:line($group), ''" separator="&#10;"/>
              </xsl:if>
              <xsl:next-iteration>
                  <xsl:with-param name="group" select="if ($new-group) then . else ($group, .)"/>
              </xsl:next-iteration>
          </xsl:iterate>
      </xsl:for-each-group>
  </xsl:template>

</xsl:stylesheet>

Online at https://xsltfiddle.liberty-development.net/pPgCcov/2.

Group by consecutive dates and sum by similar dates in xslt

1 Answers