CODEWITHSUNDEEP
xmlxsltgroup-by
Juraj Mrska
Juraj Mrska

Reputation: 13

xsl group by with when date to equals date from

I'm using xslt 2.0 for transformation to a new XML This is my xml Structure:

<?xml version="1.0" encoding="UTF-8"?>
<bills>
    <bill id="1">
        <rz_from>05.11.2011</rz_from>
        <rz_to>31.12.2011</rz_to>
        <rz_price_pro_unit>4</rz_price_pro_unit>
        <rz_units>7</rz_units>
    </bill>
    <bill id="2">
        <rz_from>1.1.2012</rz_from>
        <rz_to>31.3.2012</rz_to>
        <rz_price_pro_unit>4</rz_price_pro_unit>
        <rz_units>9</rz_units>
    </bill>
    <bill id="3">
        <rz_from>1.5.2012</rz_from>
        <rz_to>31.12.2012</rz_to>
        <rz_price_pro_unit>4</rz_price_pro_unit>
        <rz_units>21</rz_units>
    </bill>
    <bill id="4">
        <rz_from>1.1.2013</rz_from>
        <rz_to>31.12.2013</rz_to>
        <rz_price_pro_unit>5</rz_price_pro_unit>
        <rz_units>45</rz_units>
    </bill>
    <bill id="5">
        <rz_from>1.1.2014</rz_from>
        <rz_to>31.12.2014</rz_to>
        <rz_price_pro_unit>5</rz_price_pro_unit>
        <rz_units>51</rz_units>
    </bill>
</bills>

I need to group the bill nodes by these rules:

  • the price per unit is the same
  • the date (rz_from - 1 day) is equal to the date (rz_to) of the previous node
  • the count of units should by the sum of the grouped values

So the result should be:

  • Bill: 16 units - price 4 (bill 1+2)
  • Bill: 21 units - price 4 (bill 3)
  • Bill: 96 units - price 5 (bill 4+5)

Upvotes: 1

Views: 80

Answers (1)

michael.hor257k
michael.hor257k

Reputation: 116992

I think you have enough here for two or three separate questions.

First, there is no grouping as such to do here, because there is no common value you can group by. I believe you need to select the bills that will be first in each group (i.e. those that do not continue a "chain") and then apply "sibling recursion" to the members of the group.

The added complication is that your dates are not ISO-8601 dates (YYYY-MM-DD), so no calculation can be performed upon them until they are converted. I would use a custom function for this.

The following stylesheet assumes that bills are sorted in chronological order (although this assumption is not essential to solving the problem):

XSLT 2.0

<xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:my="http://www.example.com/my"
exclude-result-prefixes="xs my">
<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:function name="my:get-as-date" as="xs:date">
    <xsl:param name="dmy"/> 
    <xsl:variable name="d" select="substring-before($dmy, '.')"/> 
    <xsl:variable name="m" select="substring-before(substring-after($dmy, '.'), '.')"/> 
    <xsl:variable name="y" select="substring-after(substring-after($dmy, '.'), '.')"/>  
    <xsl:value-of select="concat(format-number(xs:integer($y), '0000'), '-', format-number(xs:integer($m), '00'), '-', format-number(xs:integer($d), '00'))"/>
</xsl:function>

<xsl:template match="/bills">
    <xsl:copy>
        <xsl:apply-templates select="bill[not(preceding-sibling::bill[1]/my:get-as-date(rz_to) + xs:dayTimeDuration('P1D') = my:get-as-date(rz_from) and preceding-sibling::bill[1]/rz_price_pro_unit = rz_price_pro_unit)]" mode="init"/>
    </xsl:copy>
</xsl:template>

<xsl:template match="bill" mode="init">
    <group>
        <xsl:copy-of select="rz_from"/>
        <xsl:apply-templates select="." mode="collect"/>
    </group>
</xsl:template>

<xsl:template match="bill" mode="collect">
    <xsl:param name="units" select="0"/>
        <xsl:choose>
            <xsl:when test="following-sibling::bill[1]/my:get-as-date(rz_from) = my:get-as-date(rz_to) + xs:dayTimeDuration('P1D') and following-sibling::bill[1]/rz_price_pro_unit = rz_price_pro_unit">
                <xsl:apply-templates select="following-sibling::bill[1]" mode="collect">
                    <xsl:with-param name="units" select="$units + rz_units"/>
                </xsl:apply-templates>
            </xsl:when>
            <xsl:otherwise>
                <xsl:copy-of select="rz_to"/>
                <xsl:copy-of select="rz_price_pro_unit"/>
                 <rz_units>
                    <xsl:value-of select="$units + rz_units"/>
                 </rz_units>
            </xsl:otherwise>
        </xsl:choose>   
</xsl:template>

</xsl:stylesheet>

Result:

<?xml version="1.0" encoding="utf-8"?>
<bills>
   <group>
      <rz_from>05.11.2011</rz_from>
      <rz_to>31.3.2012</rz_to>
      <rz_price_pro_unit>4</rz_price_pro_unit>
      <rz_units>16</rz_units>
   </group>
   <group>
      <rz_from>1.5.2012</rz_from>
      <rz_to>31.12.2012</rz_to>
      <rz_price_pro_unit>4</rz_price_pro_unit>
      <rz_units>21</rz_units>
   </group>
   <group>
      <rz_from>1.1.2013</rz_from>
      <rz_to>31.12.2014</rz_to>
      <rz_price_pro_unit>5</rz_price_pro_unit>
      <rz_units>96</rz_units>
   </group>
</bills>

Upvotes: 1

Related Questions