The IntervalMatch function
The IntervalMatch function is used to match a single value in a table to an interval or range of values in another table. This scenario occurs in real time when you are trying to match a date in one table, which falls between two dates in another table. Another example will be matching the number of shifts falling in a time period of, say, eight hours.
The IntervalMatch function is also useful in handling slowly changing dimensions (SCD), specifically Type 2.
Slowly changing dimension Type 2 is used to track the historical information of the data. For example, see the following employee records:
Employees with IDs 275 and 278 have held two positions respectively. This table is an SCD Type 2 because it stores the information for both the positions using a start date and an end date. The end date of 9/99/9999 represents that the position is still open and not end-dated.
The data modeling challenge is to find out which position or job titles they held during a specific...
