Generating future dates based on different interval or frequency.

avinashelite — Wed, 21 Sep 2022 17:07:35 GMT

Hi All,

This document illustrate how to generate the future dates/months based on different interval for each records. i.e. for different keys you need to generate different due dates , consider the e.g. below for Key 1 we need the future due date to be generate every 6 months and for Key 2 we need 3 month follow up ..

Input Data:

Key	Month Interval	Amount	Due Date	Expire Date
1	6	50	01-May-19	31-Dec-20
2	3	50	12-Dec-18	31-Dec-20
3	12	100	31-Dec-17	31-Dec-20
4	1	10	20-May-19	31-Dec-20

*For key 1 we have Month Interval as 6 so we need to dynamically generate future due date months i.e.

OUTPUT for Key 1:

Key	Month Interval	Amount	Due Date	Future Due Date	Expire Date
1	6	50	01/05/2019	01-Nov-19	31-Dec-20
1	6	50	01/05/2019	01-May-20	31-Dec-20
1	6	50	01/05/2019	01-Nov-20	31-Dec-20

Script:

DATA:
LOAD Key,
[Month Interval],
Amount,
[Due Date],
[Expire Date]
FROM
[C:\Users\avinash5\Desktop\Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join

LOAD
*,
AddMonths([Due Date] ,([Month Interval]+ ([Month Interval]*IterNo()))) as [Future Due Date]
While
AddMonths([Due Date] ,([Month Interval]*IterNo())) <= [Expire Date]; //Defines when the Interval should end

LOAD Key,
[Month Interval],
Amount,
[Due Date],
[Expire Date]
FROM
[C:\Users\avinash5\Desktop\Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Note: I have fixed Expire date , you could use different dates also.

Regards,

Avinash R

article Generating future dates based on different interval or frequency. in Member Articles

Generating future dates based on different interval or frequency.