I need to create a new table from an existing table. It needs to have a month end date for every month from the start of a record opening until it's closed. See below.
Table
Rec Number
Opened
Closed
5000
1/1/2023
5/11/2023
New Table
Rec Number
Opened
Closed
MonthEnd
5000
1/1/2023
1/31/2023
5000
1/1/2023
2/23/2023
5000
1/1/2023
3/31/2023
5000
1/1/2023
4/30/2023
5000
1/1/2023
5/11/2023
5/31/2023
The goal is to create a P50 and P90 Aging balance by month. This is the percentile value of the aging of the balance items. P90 would be 90% of the items have an age at or below the p90 value for a given month. Having the month end will allow me to calculate the days it was open as of the end of that month. There may be a better way to do this, so any recommendations would be appreciated.
// Loop through months and generate MonthEnd values FOR i = 0 TO DateDiff('M', Num(peek('Opened')), Num(peek('Closed'))) LET vMonthEnd = Date(MonthEnd(AddMonths(Num(peek('Opened')), i)), 'MM/DD/YYYY'); IF vMonthEnd > Num(peek('Closed')) THEN EXIT FOR; // Exit loop if we go beyond the Closed date ENDIF NOCONCATENATE LOAD RecNumber, Opened, Closed, '$(vMonthEnd)' AS MonthEnd RESIDENT OriginalTable; NEXT
DROP TABLE OriginalTable; // Drop the original table if not needed
