How do i derive the total cost in XSLT?

Question

I need your guidance is solving this xslt question. As always, thank you for your help.

This is an extension to my earlier question where I was asked to provide the end result. However, since the format has changed and there is some really good information and responses in the other post, I did not want to change that post.

However, the input xml file has changed. Sorry for a long post. I thought it might be useful to provide additional information.

XML Input

<Output>         
    <Transaction>
        <TID>T01</TID>
        <Employee_ID>E00001</Employee_ID>
        <Legal_Name Descriptor="John Doe" />         
        <lastName>Doe</lastName>
        <firstName>John</firstName>
        <P_From_Date>2015-01-01-08:00</P_From_Date>
        <P_To_Date>2015-12-31-08:00</P_To_Date>                           
        <Plan Descriptor="Plan A" />            
        <effective_date>2014-01-01-08:00</effective_date>
        <Annual_Cost>6000</Annual_Cost>
    </Transaction>


    <Transaction>
        <TID>T02</TID>
        <Employee_ID>E00001</Employee_ID>
        <Legal_Name Descriptor="John Doe" />         
        <lastName>Doe</lastName>
        <firstName>John</firstName>
        <P_From_Date>2015-01-01-08:00</P_From_Date>
        <P_To_Date>2015-12-31-08:00</P_To_Date>                           
        <Plan Descriptor="Plan A" />            
        <effective_date>2014-10-01-08:00</effective_date>
        <Annual_Cost>12000</Annual_Cost>
    </Transaction>


    <Transaction>
        <TID>T03</TID>
        <Employee_ID>E00001</Employee_ID>
        <Legal_Name Descriptor="John Doe" />         
        <lastName>Doe</lastName>
        <firstName>John</firstName>
        <P_From_Date>2015-0-01-08:00</P_From_Date>
        <P_To_Date>2015-12-31-08:00</P_To_Date>                           
        <Plan Descriptor="Plan B" />            
        <effective_date>2014-01-04-08:00</effective_date>
        <Annual_Cost>6000</Annual_Cost>
    </Transaction>


    <Transaction>
        <TID>T04</TID>
        <Employee_ID>E00001</Employee_ID>
        <Legal_Name Descriptor="John Doe" />         
        <lastName>Doe</lastName>
        <firstName>John</firstName>
        <P_From_Date>2015-0-01-08:00</P_From_Date>
        <P_To_Date>2015-12-31-08:00</P_To_Date>                           
        <Plan Descriptor="Plan B" />            
        <effective_date>2014-09-04-08:00</effective_date>
        <Annual_Cost>15000</Annual_Cost>
    </Transaction>


    <Transaction>
        <TID>T05</TID>
        <Employee_ID>E00001</Employee_ID>
        <Legal_Name Descriptor="John Doe" />         
        <lastName>Doe</lastName>
        <firstName>John</firstName>
        <P_From_Date>2015-0-01-08:00</P_From_Date>
        <P_To_Date>2015-12-31-08:00</P_To_Date>                           
        <Plan Descriptor="Plan B" />            
        <effective_date>2015-06-04-08:00</effective_date>
        <Annual_Cost>18000</Annual_Cost>

    </Transaction>



    <Transaction>
        <TID>T06</TID>
        <Employee_ID>E00002</Employee_ID>
        <Legal_Name Descriptor="Brad Pits" />         
        <lastName>Pits</lastName>
        <firstName>Brad</firstName>
        <P_From_Date>2015-0-01-08:00</P_From_Date>
        <P_To_Date>2015-12-31-08:00</P_To_Date>                           
        <Plan Descriptor="Plan Z" />            
        <effective_date>2012-01-04-08:00</effective_date>

        <Annual_Cost>10000</Annual_Cost>

    </Transaction>

    <Transaction>
        <TID>T07</TID>
        <Employee_ID>E00002</Employee_ID>
        <Legal_Name Descriptor="Brad Pits" />         
        <lastName>Pits</lastName>
        <firstName>Brad</firstName>
        <P_From_Date>2015-0-01-08:00</P_From_Date>
        <P_To_Date>2015-12-31-08:00</P_To_Date>                           
        <Plan Descriptor="Plan Z" />            
        <effective_date>2013-01-04-08:00</effective_date>
        <end_date>2015-09-30-08:00</end_date>
        <Annual_Cost>15000</Annual_Cost>

    </Transaction>

    <Transaction>
        <TID>T08</TID>
        <Employee_ID>E00001</Employee_ID>
        <Legal_Name Descriptor="John Doe" />         
        <lastName>Doe</lastName>
        <firstName>John</firstName>
        <P_From_Date>2015-0-01-08:00</P_From_Date>
        <P_To_Date>2015-01-31-08:00</P_To_Date>                           
        <Plan Descriptor="Plan B" />            
        <effective_date>2015-09-04-08:00</effective_date>
        <end_date>2015-11-30-08:00</end_date>
        <Annual_Cost>24000</Annual_Cost>

    </Transaction>

    <Transaction>
        <TID>T09</TID>
        <Employee_ID>E00003</Employee_ID>
        <Legal_Name Descriptor="Tim Baktoo" />         
        <lastName>Baktoo</lastName>
        <firstName>Tim</firstName>
        <P_From_Date>2015-0-01-08:00</P_From_Date>
        <P_To_Date>2015-01-31-08:00</P_To_Date>                           
        <Plan Descriptor="Plan B" />            
        <effective_date>2010-09-04-08:00</effective_date>
        <end_date>2014-10-31-08:00</end_date>
        <Annual_Cost>24000</Annual_Cost>

    </Transaction>

    <Transaction>
        <TID>T11</TID>
        <Employee_ID>E00003</Employee_ID>
        <Legal_Name Descriptor="Tim Baktoo" />         
        <lastName>Baktoo</lastName>
        <firstName>Tim</firstName>
        <P_From_Date>2015-0-01-08:00</P_From_Date>
        <P_To_Date>2015-01-31-08:00</P_To_Date>                           
        <Plan Descriptor="Plan C" />            
        <effective_date>2016-01-01-08:00</effective_date>
        <end_date>2014-10-31-08:00</end_date>
        <Annual_Cost>24000</Annual_Cost>

    </Transaction>



</Output>

Output Expected

<Employees>
  <Employee>
     <ID>E00001</ID>
     <Amount>28586</Amount>
  </Employee>

  <Employee>
     <ID>E00002</ID>
     <Amount>11219</Amount>
  </Employee>

  <Employee>
     <ID>E00004</ID>
     <Amount>12000</Amount>
  </Employee>


</Employee>

Additional Information

Transactions for employees are not necessarily ordered. I do not have control over the file. Thus transactions for a plan for an employee for different effective dates are no necessarily together or in ascending or descending order of effective date. 2 Transactions are for different plans and are effective a certain date 3. The effective date indicates the date from which that transction is effective for the employee for a specific plan. 4. The transaction is effective from the effective_date through the next effective date of the transaction(if there is one ) and may end earlier if there is an end date on the transaction.
5. The transaction could have effective dates before the P_From_Date or after the P_To_Date. 6. Similarly, the End_Date could be before the P_From_Date or after the P_To_Date.

      Objective
      *********
      Determine the total amount for each employee for the period between P_From_Date and P_To_Date. 

      See expected output.

      Other Notes


      1. The total cost is to be determined for the period between P_From_Date and P_To_Date.
      2. The general formula to derive the cost for each transaction for a plan for an employee is as follows
           No of days div 365 or 366 for leap year * total_cost.
           Use P_To_Date for leap year determination.

           This will be added up for that employee to determine employee total cost.
      2. If there are trasactions with effective date before the p_from date, they will not impact the total cost
         unless that transaction is effective for part of the period between P_From_Date and P_To_Date. 
         If the end date of this transaction falls before the p_From_Date, then this transaction will also 
         not impact the cost as it is no longer effective.
      3. Transactions with effective_date between P_From_Date and P_To_Date will be included in determining the total cost as follows
          1. The No Of Days for which the cost will be determines is as follows.
             From date - will be the effective date of the transaction.
             To Date -  If the transaction has an end_date and the end date falls after P_to_Date, then P_To_Date
                        else, end_date

                        If the transaction does not have an end date But there is another transaction for same employee and plan
                        with an effective date grater than the effective date of this transaction and the effective date of
                        that other transction falls after then P_To_Date, the To Date will be P_To-Date. Else it is the
                         effective date of that other transaction - 1 day.

                        Period (No Of Days) = To date - From date + 1day.                       

      4. Similarly, if the end date of the transaction falls after the P_To-Date, then that end date is not relevant
        in the computation of the period as the employee will be charged upto p_to_date as long as the transaction is effective during
        the P_From_Date and P_To-Date

Answer 1

I have voted to close this question as too broad, but I wanted to give you a starting point. The following stylesheet:

XSLT 2.0

<xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>

<xsl:template match="/Output">
    <Employees>
        <xsl:for-each-group select="Transaction" group-by="Employee_ID">
            <Employee>
                <ID>
                    <xsl:value-of select="current-grouping-key()"/>
                </ID>
                <Amount>
                    <xsl:value-of select="sum(current-group()/Annual_Cost)"/>
                </Amount>
            </Employee>
        </xsl:for-each-group>
    </Employees>  
</xsl:template>

</xsl:stylesheet>

when applied to your input, will return:

<?xml version="1.0" encoding="UTF-8"?>
<Employees>
   <Employee>
      <ID>E00001</ID>
      <Amount>81000</Amount>
   </Employee>
   <Employee>
      <ID>E00002</ID>
      <Amount>25000</Amount>
   </Employee>
   <Employee>
      <ID>E00003</ID>
      <Amount>48000</Amount>
   </Employee>
</Employees>

There are two ways you could proceed from hre:

1. Instead of summing the amount, sum a calculation that references the Annual_Cost (and possibly other nodes in the Transaction). For example, you could use:

   <Amount>
       <xsl:value-of select="sum(current-group()/(2 * Annual_Cost))"/>
   </Amount>
   

   to double the values;

2. If the calculation is too complex, you can do a preliminary pass of the transactions into a variable, calculating the actual amounts you wish to summarize; then group the resulting nodes by employee, as shown above.

Answer 2

XPath has a sum function. Not tested, but I assume you should do something like this:

<xsl:value-of select="sum(Annual_Cost)" />

Of course, change Annual_Cost here by a more accurate XPath node-set if you need : Only those coming from a transaction where Employee_ID is the ID tag value.