Calculating aging bucket (moving)

[#1]

All,

I got some case.

I have a system that will generate all my payment term and due date, for example, Im paying for my car for 6 times payment, which:

Payment # | Due Date | Payment Date

1. 12 Jan 2011    20 Jan 2011

2. 12 Feb 2011     2 Mar 2011

3. 12 Mar 2011     20 Apr 2011

4. 12 Apr 2011     (no payment till end)

5. 12 Mei 2011    

6. 12 Jun 2011     

Then the bucket (periodic) that i should generate:

EoM Jan 2011: 0 (No overdue in the end of Jan 2011)

EoM Feb 2011: 16 days (28 Feb 2011 - 12 Feb 2011)

EoM Mar 2011: 19 days (31 Mar 2 - 12 Mar 2011)

EoM Apr 2011: 18 days (30 Apr - 12 Apr)

EoM Mei 2011: 49 days (31 Mei - 12 Apr)

EoM Jun 2011: 80 days (30 June - 12 Apr)

Help please >.<

[#2 · Accepted solution]

I have solved this, the only way to solve this is by generating snapshot data in each end of month period.

In case if anyone encounter this problem, you can ask me.