Databases Reference
In-Depth Information
The use case
We know that the
IntervalMatch
function makes use of closed intervals already
defined in a table. What happens if all we have is a start date? To illustrate this
scenario, look at the following screenshot:
As you can see, the
End Date
field has disappeared. However, there is a way for
us to guess it and assign the corresponding value, based on the start date of the
immediate following record. That is, if one record starts on 1-Feb-1998 and the
immediate following starts on 1-Jan-2000, it means that the first interval ended on
31-Dec-1999, right?
In order for us to calculate the end date, we need to first sort the table values so that
all corresponding records are contiguous, then "peek" at the start value from the
next (or previous, if ordered backwards) record, subtract one day and that will be
our new end date. After that, we are now able to use the
IntervalMatch
function to
expand those intervals.
To complete the challenge, make use of the same
Carrier Decode
table we have
used previously, only ignore the
End Date
field as if it was not there. You will also
be able to compare your results with those we came up with in the previous section.
Good Luck!
