Thanks in advance for any help.
I want to calculate the sum of an amount for the last day of each month for every account which would have been expired but not settled and to show the value of that account every month past expiry date where the account would still have been active.
The table below would be the desired outcome.
| AccountNo | Jan2014 | Feb2014 | Mar2014 | Apr2014 | May2014 | Jun2014 | Jul2014 | Aug2014 |
|---|
| 1 | | 100 | 100 | 100 | 100 | | | |
| 2 | | | 2000 | 2000 | 2000 | 2000 | 2000 | 2000 |
| 3 | | | | 1000 | 1000 | | | |
| 4 | | | 100 | | | | | |
The example above is:
1 Expiry Date was 15 Feb 2014 and Settled on 3 June 2014.
2 Expiry Date was 1 Mar 2014 and is still not settled so the settled date is a null Value or the status is C
3 Expiry Date was 2 Apr 2014 and Written Off 2 June 2014
4 Expiry Date was 1 Mar and Renewed on 2 April.
So we have a number of date fields I want to use, Expiry, Settled, WrittenOff, and Renewed of which the last 3 could be null depending on the outcome.
Again, many thanks for the steer.
