Let's review different pros and cons.
Imagine that we have employee Vacations and a company Calendar, like this:
SET DateFormat='M/D/YYYY';
Vacation:
Load * Inline [
EmployeeId, StartDate, EndDate
1,1/1/2009, 1/15/2009
2,1/10/2009,1/14/2009
3,1/2/2009,1/4/2009
3,1/15/2009,1/25/2009
4,1/1/2009, 1/15/2009
];
Let vStartDate = num(date('1/1/2009'));
Let vEndDate = num(date('1/31/2009'));
Calendar:
load $(vStartDate) + RecNo() - 1 as Date
autogenerate vEndDate - vStartDate + 1;
left join
load Date,
year(Date) as Year,
month(Date) as Month
resident Calendar;
How to tie Calendar to Vacations? That's right - use IntervalMatch.
We can do this by different ways:
1)
left join (Vacation)
intervalmatch (Date) load StartDate, EndDate resident Vacation;
pros: no surrogate keys
cons: vacation record duplication for number of dates in the range
2)
VacationToDate:
intervalmatch (Date) load StartDate, EndDate resident Vacation;
pros: no duplications, cross-reference table, easy to read the logic
cons: surrogate key (hate surrogate keys)
3) But there is another way to do this without IntervalMatch. If you need something good, do it by yourself 🙂
left join (Vacation)
load StartDate
,EndDate
,autonumber(StartDate & '-' & EndDate) as DateRangeId
resident Vacation;
VacationToDate:
load distinct
DateRangeId,
StartDate + IterNo() - 1 as Date
resident Vacation
while StartDate + IterNo() - 1 <= EndDate;
pros: all pros mentioned above
cons: no
Good luck
