Iterno based on YearMonth instead of days

[amien]

t2:
Load
key_id,
from + IterNo()-1 as Date,
from,
till,
1 as #Counter
Resident t1
While from + IterNo()-1 <= Num(till)
;

I have this code, as a replacement for intervalmatch, to give me a list of all key_id with all the dates where this key is 'active' based on from and till. This works fine, but it will generate 30mil records. After this i reduced it because i only need it on a monthly bases.

Is it possible to alter to IterNo script so it will do the same but only based on a month (and therefor not temporary generate 30mil records)

Thanks in advanced

[marcus_sommer · Accepted solution]

Maybe this one will work:

...

monthname(addmonths(monthstart(from), iterno()-1)) as MonthYear,

...

While num(addmonths(monthstart(from), iterno()-1)) <= Num(till);

- Marcus

[amien]

Thanks Marcus