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
