Hi Don,

Thanks a lot for the above - I have tested that and it works really nicely for what I need - there is just one scenario which I don't think I explained too well before;

There could be a scenario where we don't have the very first record i.e. the reporting day record, so we would have to create the records before based on a Reporting date parameter.

For example

Input record - Note that Reporting date is 2015-12-01;

Code:

COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18
C,1234,TEST,1,AA,AAAA,2019-12-01,2020-11-30,190425.71,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2020-11-30

As we don't have the reporting date record, we would need to create that as below, right up until Maturity date, or in this case, the final record;

What we are trying to say in the below example is that up until the final record, no units were used in the previous years. Therefore we need to create records that reflect this - units used (COL9) will be 0 as nothing has used, and the outstanding balance should be calculated by adding units (COL9) to outstanding balance (COL12) of the record we know.

Note:
COL9 - Units used that year
COL12 - Outstanding Balance i.e. Units remaining

Desired output;

Code:

COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18
C,1234,TEST,1,AA,AAAA,2015-12-01,2016-11-30,0,AAA,P,4496015.93,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,1234,TEST,1,AA,AAAA,2016-12-01,2017-11-30,0,AAA,P,4496015.93,NULL,NULL,NULL,NULL,NULL,2020-11-30 
C,1234,TEST,1,AA,AAAA,2017-12-01,2018-11-30,0,AAA,P,4496015.93,NULL,NULL,NULL,NULL,NULL,2020-11-30 
C,1234,TEST,1,AA,AAAA,2018-12-01,2019-11-30,0,AAA,P,4496015.93,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,1234,TEST,1,AA,AAAA,2019-12-01,2020-11-30,190425.71,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2020-11-30  -- Original input record

On the same hand, we could also have a scenario where we have used x amount of units half way through the term, and then again, nothing right up until maturity date;

For example

Input record - Note again reporting date is 2015-12-01;

Code:

COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18
C,1234,TEST,1,AA,AAAA,2017-12-01,2018-11-30,190425.71,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2020-11-30

In this scenario we would have to add in records before (starting from reporting date) again using the same logic as above (COL9 set to 0 and COL12 being worked out as COL9+COL12) - We would also have to add records in after the record that we know of i.e. up until Maturity date. In this case, as you have kindly done in your previous bit of code COL9 would be set to 0 and COL12 would take the value of the previous record.

Desired output;

Code:

COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18
C,1234,TEST,1,AA,AAAA,2015-12-01,2016-11-30,0,AAA,P,4496015.93,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,1234,TEST,1,AA,AAAA,2016-12-01,2017-11-30,0,AAA,P,4496015.93,NULL,NULL,NULL,NULL,NULL,2020-11-30 
C,1234,TEST,1,AA,AAAA,2017-12-01,2018-11-30,190425.71,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2020-11-30 -- Original input record
C,1234,TEST,1,AA,AAAA,2018-12-01,2019-11-30,0,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,1234,TEST,1,AA,AAAA,2019-12-01,2020-11-30,0,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2020-11-30

In a nut shell, what we're trying to work out here is for each reporting year up until maturity date, how many units have been used, and how many do we have remaining.

Really appreciate your help on this one Don, it's certainly a lot more complicated that we first envisaged