Autogenerating records for dates

[#1]

In my script I have a portion that attempts to create records in a table that go back 1 year from the next month.

So today is 10/17/2012. I want to make a table with the following:

11/30/2012

10/31/2012

9/30/2012

8/31/2012

7/31/2012

6/30/2012

5/31/2012

4/30/2012

3/31/2012

2/29/2012

1/31/2012

12/31/2011

11/30/2011

It needs to always be at the end of the month since that is my rollover date.

I sort of have it, but I think my logic for jumping forward during my LOAD is the issue. Problem is, I don't know what my other option would be?

In the code below, varSRMaxCreateDate is just an active variable that gets the latest record date in the file.

LET RollMax = Num(MonthEnd(varSRMaxCreateDate));

LET RollMin = Num(MonthEnd(Addmonths(varSRMaxCreateDate,-12)));

trace RollMin: $(RollMin);

trace RollMax: $(RollMax);

RollingCalendar:

LOAD

Date($(RollMin) + RecNo() * 30) AS RollDate,

1 as RollKey

AUTOGENERATE 13;

This is the resulting data. You can see how not all months are at the ending date.

RollDate

12/1/2011

12/31/2011

1/30/2012

2/29/2012

3/30/2012

4/29/2012

5/29/2012

6/28/2012

7/28/2012

8/27/2012

9/26/2012

10/26/2012

11/25/2012

[johnw · Accepted solution]

I think this:

date(daystart(addmonths($(RollMin),recno()-1,1))) as RollDate

[#3]

John,

That is beautiful! It worked perfectly!

Thanks!