I am trying to create a couple nested loops in my code to group by Time_Data/Time_Off/Code. I want to display all Time Off Codes that fall within consecutive days on 1 line in the output. Any additional Time Off Code whether that be individual days or 2 consecutive days would be output on their own lines respectively. In this example, I provide 2 codes where one code has 3 consecutive days, and another that has 4 consecutive days. Additionally, the example shows 2 codes that appear with no consecutive days.
The examples will hopefully help provide clarity.
Input XML:
<?xml version="1.0" encoding="utf-8"?>
<Output xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Record>
<Employee_ID>123456</Employee_ID>
<Payroll_ID>1111</Payroll_ID>
<Time_Data>
<Date>2023-11-04</Date>
<Day>Saturday</Day>
<Time_Off>
<Code>DL</Code>
<Date>2023-11-04</Date>
<Quantity>7</Quantity>
<Unit>HOURS</Unit>
</Time_Off>
<Time_Off>
<Code>RJ</Code>
<Date>2023-11-04</Date>
<Quantity>1</Quantity>
<Unit>HOURS</Unit>
</Time_Off>
</Time_Data>
<Time_Data>
<Date>2023-11-05</Date>
<Day>Sunday</Day>
<Time_Off>
<Code>DL</Code>
<Date>2023-11-05</Date>
<Quantity>7</Quantity>
<Unit>HOURS</Unit>
</Time_Off>
<Time_Off>
<Code>RJ</Code>
<Date>2023-11-05</Date>
<Quantity>1</Quantity>
<Unit>HOURS</Unit>
</Time_Off>
</Time_Data>
<Time_Data>
<Date>2023-11-06</Date>
<Day>Monday</Day>
<Time_Off>
<Code>DL</Code>
<Date>2023-11-06</Date>
<Quantity>7</Quantity>
<Unit>HOURS</Unit>
</Time_Off>
<Time_Off>
<Code>RJ</Code>
<Date>2023-11-06</Date>
<Quantity>1</Quantity>
<Unit>HOURS</Unit>
</Time_Off>
</Time_Data>
<Time_Data>
<Date>2023-11-07</Date>
<Day>Tuesday</Day>
<Time_Off>
<Code>RJ</Code>
<Date>2023-11-07</Date>
<Quantity>1</Quantity>
<Unit>HOURS</Unit>
</Time_Off>
</Time_Data>
<Time_Data>
<Date>2023-11-15</Date>
<Day>Monday</Day>
<Time_Off>
<Code>DL</Code>
<Date>2023-11-15</Date>
<Quantity>7</Quantity>
<Unit>HOURS</Unit>
</Time_Off>
<Time_Off>
<Code>RJ</Code>
<Date>2023-11-15</Date>
<Quantity>1</Quantity>
<Unit>HOURS</Unit>
</Time_Off>
</Time_Data>
</Record>
</Output>
XSL transformation:
<xsl:for-each select="Output/Record">
<xsl:for-each-group select="Time_Data/Time_Off" group-by="concat(Code, '|', Date)">
<xsl:for-each-group select="current-group()" group-starting-with="*[not(xs:date(Date) = xs:date(preceding-sibling::*[1]/Date) + xs:dayTimeDuration('P1D'))]">
<xsl:value-of select="format-number(../../Payroll_ID, '00000000')"/>
<xsl:value-of select="' '"/>
<xsl:value-of select="format-date(Date, '[D01][M01]')"/>
<xsl:value-of select="format-date(current-group()[last()]/Date, '[D01][M01]')"/>
<xsl:value-of select="' '"/>
<xsl:value-of select="format-number(sum(current-group()/Quantity) * 100, '0000')"/>
<xsl:value-of select="Code"/>
<xsl:value-of select="'TT'"/>
<xsl:value-of select="$linefeed"/>
</xsl:for-each-group>
</xsl:for-each-group>
</xsl:for-each>
Output I'm receiving:
00001111 04110411 0700DLTT
00001111 04110411 0100RJTT
00001111 05110511 0700DLTT
00001111 05110511 0100RJTT
00001111 06110611 0700DLTT
00001111 06110611 0100RJTT
00001111 07110711 0100RJTT
00001111 15111511 0700DLTT
00001111 15111511 0100RJTT
Output I'm hoping to achieve:
00001111 04110611 2100DLTT
00001111 04110711 0400RJTT
00001111 15111511 0700DLTT
00001111 15111511 0100RJTT
Can anyone provide some guidance as to how I can achieve this?
I have tried to nest multiple for-each-groups which are grouped by the Code, but am not able to return the correct number of rows. The loop is iterating too many times and I'm not understanding the correct grouping to achieve the desired results.
EDIT: Thank you to michael.hor257k for the answer. Here is the XSL loop that worked for me.
<xsl:for-each select="Output/Record">
<xsl:for-each-group select="Time_Data/Time_Off" group-by="Code">
<xsl:for-each-group select="current-group()" group-adjacent="xs:date(Date) - position()*xs:dayTimeDuration('P1D')">
<xsl:value-of select="format-number(../../Payroll_ID, '00000000')"/>
<xsl:value-of select="' '"/>
<xsl:value-of select="format-date(Date, '[D01][M01]')"/>
<xsl:value-of select="format-date(current-group()[last()]/Date, '[D01][M01]')"/>
<xsl:value-of select="' '"/>
<xsl:value-of
select="format-number(sum(current-group()/Quantity) * 100, '0000')"/>
<xsl:value-of select="current-group()[1]/Code"/>
<xsl:value-of select="'TT'"/>
<xsl:value-of select="$linefeed"/>
</xsl:for-each-group>
</xsl:for-each-group>
</xsl:for-each>
Assuming that within the initial group there will be no duplicate dates, and that the records are already sorted chronologically, you should be able to perform the secondary grouping using:
<xsl:for-each-group select="current-group()" group-adjacent="xs:date(Date) - position()*xs:dayTimeDuration('P1D')">
This should create a group for any sequence of consecutive dates (untested with your data).