I have a situation where my table rows have a "valid from date" and a "valid to date". I need to be able to show all individual dates in the next x years, and what rows of the table are valid for each date. So, if the row has a valid from date of May 1st, 2011 and a valid to date of October 31st, 2011, I need to be able to see each of the 180-or-so dates, one by one, and see that this particular row is valid for that particular day. (The YldSuffix is the row identifier.) There can be many valid rows for each individual date, and many individual dates for each valid row, in case that makes any difference.
To get all the individual dates, I created a Master Calendar, but I don't appear to be able to reference it in a table load so that I can do a comparison. Here is the script I wrote:
min(YldFromDate) as minDate,
max(YldToDate) as maxDate
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
$(varMinDate) + IterNo()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
TempDate AS "RoomNightValidDate",
weekend(TempDate) As "RoomNightValidWE",
Year(TempDate) As "RoomNightValidYear",
Month(TempDate) As "RoomNightValidMonth",
WeekDay(TempDate) as "RoomNightValidDOW"
Order By TempDate ASC;
Drop Table TempCalendar;
"suffix_no" as "YldSuffix",
TempDate AS "RoomNightValidDate"
where b.TempDate >= a.to_date and b.TempDate <= a.from_date
SQL SELECT *
FROM fdgbrtover a, MasterCalendar b;
The script errors out and keeps telling me that the MasterCalendar is an invalid object. I find it hard to believe that you can create a calendar but not use it for any comparative purpose. Am I wrong?
I suspect that I am just messing up the syntax, and any assistance would by most appreciated.
Again the collective wisdom and kindness of this group has helped me immensely. Thank you for pointing me in the right direction...that being IntervalMatch. Using the blog post that Bill Markham posted and then, apparently, subsequently deleted, I was able to make this work perfectly.
For future reference, this is the blog post I used to get this to work.