Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
I think this:
date(daystart(addmonths($(RollMin),recno()-1,1))) as RollDate
I think this:
date(daystart(addmonths($(RollMin),recno()-1,1))) as RollDate
John,
That is beautiful! It worked perfectly!
Thanks!