The inverse of this relationship is: P
2
[overlaps
À1
]P
1
. In the
superscripted relationship, the first time period is the later one.
The predicate for this relationship, as it holds between two time
periods expressed as pairs of dates using the closed-open con-
vention, is:
(eff_beg_dt
1
> eff_beg_dt
2
)
AND (eff_beg_dt
1
< eff_end_dt
2
)
AND (eff_end_dt
1
> eff_end_dt
2
)
It says that P
1
starts after P
2
starts and before P

2
ends, and
ends after P
2
ends.
Consider the following request for information: which
policies began before the Diabetes Management Wellness Pro-
gram for 2009, and ended while that program was still going on?
The SQL written to fulfill this request is:
SELECT * FROM V_Allen_Example
WHERE pol_eff_beg_dt < wp_eff_beg_dt
AND pol_epis_end_dt > wp_eff_beg_dt
AND pol_epis_end_dt < wp_epis_end_dt
P
1
[intersects] P
2
This not an Allen relationship. It is the node in our taxonomy
of Allen relationships which includes the [starts], [starts
À1
],
[finishes], [finishes
À1
], [during], [during
À1
], [equals], [overlaps]
and [overlaps
À1
] relationships. In other words, it combines the
[overlaps] relationships with the [

fills] relationships.
In the non -superscripted relationship, the first time period is
the earlier one. The predicate for this relationship, as it holds
between two time periods expressed as pairs of dates using the
closed-open convention, is:
(eff_beg_dt
1
<¼ eff_beg_dt
2
)
AND (eff_end_dt
1
> eff_beg_dt
2
)
It says that P
1
starts no later than P
2
starts, and ends after P
2
starts. The idea behind it is that it includes every relationship in
which P
1
and P
2
have at least one clock tick in common and in
which P
1
is the earlier time period.

The limiting case is that in which P
1
ends at the same time
P
2
starts. So let P
1
be [4/15/2010 – 5/13/2010] and let P
2
be
[5/12/2010 – 9/18/2010]. The clock tick they share is 5/12/2010.
The inverse of this relationship is: P
1
[intersects
À1
]P
2
. The
first time period in this non-superscripted relationship is the
later one. The predicate for this relationship, as it holds between
326 Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES
two time periods expressed as pairs of dates using the closed-
open convention, is:
(eff_beg_dt
1
>¼ eff_beg_dt
2
)
AND (eff_beg_dt
1

< eff_end_dt
2
)
It says that P
2
starts no later than P
1
starts, and ends after P
1
starts. The idea behind it is that it includes every relationship in
which P
1
and P
2
have at least one clock tick in common and in
which P
1
is the earlier time period.
All pairs of time periods that share at least one clock tick sat-
isfy one or the other of these two predicates. So the predicate
that expresses the truth condition for all time periods that share
at least one clock tick is:
((eff_beg_dt
1
< eff_end_dt
2
)
AND (eff_end_dt
1
> eff_beg_dt

2
))
It says that either one of the clock ticks in P
1
is also in P
2
or that
one of the clock ticks in P
2
is also in P
1
. The idea behind it is that it
covers all the cases where two time periods have at least one clock
tick in common, regardless of which is the later time period.
It is interesting to look at this relationship in terms of what
falls outside its scope. For any two relationships that share at
| |
| |
Equals
| |
| |
Intersects
OverlapsFills
Occupies
Starts Finishes
During
| |
| |
Aligns
| |

| |
| |
| |
Time Period Relationships
Along a Common Timeline
Figure 14.10 P
1
[intersects] P
2
.
Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES 327
least one clock tick, neither ends before the other begins. Other-
wise, they could not share a clock tick. Looking at [
includes] in
terms of what falls outside its scope, we can express it as follows:
NOT(eff_end_dt
1
<¼ eff_beg_dt
2
) AND NOT(eff_beg_dt
1
>¼ eff_end_dt
2
)
And for those who like as few NOTs as possible, a logical rule
(one of the transformation rules known as the De Morgan’s equi-
valences) gives us the following predicate:
NOT((eff_end_dt
1
<¼ eff_beg_dt

2
) OR (eff_beg_dt
1
>¼ eff_end_dt
2
))
In other words, if two things are both not true, then it isn’t
true that either of them is true! On such profundities are the
rules of logic constructed.
Consider the following request for information: which
policies share any clock tick with the Diabetes Management
Wellness Program for 2009?
The SQL written to fulfill this request is:
SELECT * FROM V_Allen_Example
WHERE pol_eff_beg_dt < wp_epis_end_dt
AND pol_epis_end_dt > wp_eff_beg_dt
Notice how this SQL is much simpler than the OR’d collection
of all of the conditions that make up the leaf nodes of its Allen
relationships.
P
1
[before] P
2
This is a pair of relationships, one the inverse of the other.
In the non-superscripted relationship, the first time period is
the earlier one.
Time Period Relationships
Along a Common Timeline
Excludes
Before

| | | |
Figure 14.11 P
1
[before] P
2
.
328 Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES
The predicate for this relationship, as it holds betw een two
time periods expressed as pairs of dates using the closed-open
convention, is:
(eff_end_dt
1
< eff_beg_dt
2
)
It says that after P
1
ends, there is at least one clock tick before
P
2
begins. For example, consider the case where eff_end_dt
1
is
5/13/2014 and eff_beg_dt
2
is 5/14/2014. Because of the closed-
open convent ion, the last clock tick in P
1
is 5/12/2014, and so
there is one clock tick gap between the two time periods, that

clock tick being 5/13/2014.
The inverse of this relationship is: P
1
[before
À1
]P
2
. In the
superscripted relationship, the first time period is the later one.
The predicate for this relationship, as it holds between two time
periods expressed as pairs of dates using the closed-open
convention, is:
(eff_beg_dt
1
> eff_end_dt
2
)
It says that before P
1
begins, there is at least one clock tick
after P
2
ends. For example, consider the case where eff_beg_dt
1
is 5/14/2014 and eff_end_dt
2
is 5/13/2014. Because of the
closed-open convention, the last clock tick in P
2
is 5/12/2014,

and so there is one clock tick gap between the two time periods,
that clock tick being 5/13/2014.
Throughout this book, if it isn’t important which time period
comes first, we will simply say that the two time periods are non-
contiguous.
This is a particularly useful pair of relationships because they
distinguish episodes of the same object from one another.
Two adjacent versions—versions of an object with no other ver-
sion of the same object between them—belong to different
episodes just in case the earlier one is [before] the later one.
Of two adjacent episodes of the same object, one is [before]
the other, and the other is [before
À1
] the former.
Consider the following request for information: which
policies ended at least one date before the Diabetes Manage-
ment Wellness Program for 2009 began?
The SQL written to fulfill this request is:
SELECT * FROM V_Allen_Example
WHERE pol_epis_end_dt < wp_eff_beg_dt
P
1
[meets] P
2
This is a pair of relationships, one the inverse of the other.
In the non-superscripted relationship, the first time period is
the earlier one.
Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES 329
The predicate for this relationship, as it holds between two
time periods expressed as pairs of dates using the closed-open

convention, is:
(eff_end_dt
1
¼ eff_beg_dt
2
)
It says that after P
1
ends, P
2
begins on the very next clock tick.
There is no clock tick gap between them. Say that both dates are
5/13/2004. This means that the last clock tick in P
1
is 5/12/2004
and the first clock tick in P
2
is 5/13/2004, and so there are no
clock ticks between the two time periods.
The inverse of this re lationship is: P
2
[meets
À1
]P
1
. In the super-
scripted relationship, the first time period is the later one. The p r ed-
icate for this relationship , as it holds between two time periods
expressed as pairs of dates using the closed-open convention, is:
(eff_beg_dt

1
¼ eff_end_dt
2
)
It says that before P
1
begins, P
2
ends on the previous clock
tick. There is no clock tick gap between them.
This is a particularly useful relationship because it defines a
collection of versions of the same object that belong to the same
episode. Every adjacent pa ir of versions of the same object that
do not share any clock ticks, i.e. in which neither includes the
other, and which also do not have a single clock tick between
them, belong to the same episode. The earlier version of the pair
meets the later one; the later version is met by the earlier one.
Throughout this book, if it isn’t important which of two time
periods that meet come first, we will simply say that the two
time periods are contiguous.
Consider the following request for information: which policies
ended immediately before the Diabetes Management Wellness
Program for 2009 began?
Time Period Relationships
Along a Common Timeline
Excludes
| | |
Meets
Figure 14.12 P
1

[meets] P
2
.
330 Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES
The SQL written to fulfill this request is:
SELECT * FROM V_Allen_Example
WHERE pol_epis_end_dt ¼ wp_eff_beg_dt
P
1
[excludes] P
2
This not an Allen relationship. It is the node in our taxonomy
of Allen relationships which includes the [before], [before
À1
],
[meets] and [meets
À1
] relationships.
In the non-superscripted relationship, the first time period is
the earlier one. The predicate for thi s relationship, as it holds
between two time periods expressed as pairs of dates using the
closed-open convention, is:
(eff_end_dt
1
<¼ eff_beg_dt
2
)
It says that P
2
starts either immediately after the end of P

1
,or
later than that. The idea behind it is that it includes every rela-
tionship in which P
1
and P
2
have no clock ticks in common
and in which P
1
is the earlier time period.
The inverse of this relationship is: P
1
[excludes
À1
]P
2
. The first
time period in this non-superscripted relationship is the later
one. The predicate for this relationship, as it holds between
two time periods expressed as pairs of dates using the closed-
open convention, is:
(eff_beg_dt
1
>¼ eff_end_dt
2
)
It says that P
2
ends either immediately before the start of P

1
,
or earlier than that. The idea behind it is that it includes every
relationship in which P
1
and P
2
have no clock ticks in common
and in which P
1
is the later time period.
All pairs of time periods that share no clock ticks satisfy one
or the other of these two predicates. So the predicate that
Time Period Relationships
Along a Common Timeline
Excludes
Before Meets
| | | |
| | |
Figure 14.13 P
1
[excludes] P
2
.
Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES 331
designates all and only those time periods that share no clock
ticks is:
(eff-end-dt
1
<¼ eff-beg-dt

2
) OR (eff-beg-dt
1
>¼ eff-end-dt
2
)
It says that either P
2
starts after P
1
ends or ends before P
2
starts. The idea behind it is that regardless of which time period
comes first, they share no clock ticks.
It should be the case that two time periods [
exclude] one
another if and only if they do not [
intersect] one another. If so,
then if we put a NOT in front of the predicate for the [
intersects]
relationship, we should get a predicate which expresses the
[
excludes] relationship.
2
Putting a NOT in front of the [intersects]
relationship, we get:
NOT((eff_beg_dt
1
< eff_end_dt
2

)
AND (eff_end_dt
1
> eff_beg_dt
2
))
This is a statement of the form NOT(X AND Y). The first thing
we will do is transform it, according to the De Morgan’s rules,
into (NOT-X OR NOT-Y). This gives us:
NOT(eff_beg_dt
1
< eff_end_dt
2
)
OR NOT(eff_end_dt
1
> eff_beg_dt
2
)
Next, we can replace NOT(eff_beg_dt
1
< eff_end_dt
2
) with
(eff_beg_dt
1
>¼ eff_end_dt
2
), and NOT(eff_end_dt
1

> eff_beg_dt
2
)
with (eff_end_dt
1
<¼ eff_beg_dt
2
). This gives us:
(eff_beg_dt
1
>¼eff_end_dt
2
) OR (eff_end_dt
1
<¼ eff_beg_dt
2
)
Finally, by transposing the two predicates, we get:
(eff_end_dt
1
<¼ eff_beg_dt
2
) OR (eff_beg_dt
1
>¼ eff_end_dt
2
)
And this is indeed the predicate for the [excludes] relation-
ship, demonstrating that [
excludes] is indeed logically equivalent

to NOT[
intersects].
Consider the following request for information: which
policies either ended before the Diabetes Management Wellness
Program for 2009 began, or began after that program ended?
The SQL written to fulfill this request is:
SELECT * FROM V_Allen_Example
WHERE (pol_epis_end_dt <¼ wp_eff_beg_dt
OR pol_eff_beg_dt >¼ wp_epis_end_dt)
2
Since, at the time we are writing this paragraph, we haven’t done this, it is an excellent
way of finding out if we have made any logical mistakes so far.
332 Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES
Point in Time to Period of Time Queries
A point in time is a period of time that includes only one
clock tick. Thus, using the closed-open convention, a point in
time, T
1
, is identical to the period of time [T
1
–T
2
] where T
2
is
the next clock tick after T
1
. The only difference is in the notation.
In the following discussions, we will use the simpler notation,
T

1
, for the point in time.
In this section, we consider periods of time that are longer
than a single clock tick. Periods of time that are one clock tick
in length are points in time, and we consider Allen relationships
between two points in time later.
Given that P
1
is longer than a single clock tick, it may or may
not share a clock tick with T
1
. If it does, then T
1
[occupies] P
1
.
Otherwise, either one is [before] the other, or else they [meet].
In Asserted Versioning databases, all temporal periods are
delimited with the same point in time granularities. When compar-
ing time periods to time periods, the logic in the AVF does not
depend on the granularity of the clock ticks used in temporal
parameters, as long as all of them are the same. The clock ticks
could be months (as they are in the examples throughout this book),
days, seconds or microseconds of any size. As we noted in Chapter3,
the AVF can carry out its temporal logic without caring about
granularity specifically because of the closed-open convention.
However, when comparing a point in time to a period of time,
we must be aware of the granularity of the clock tick, and mus t
often either add a clock tick to a point or period in time, or sub-
tract a clock tick from a point or period in time. Consequently,

we need to specify the clock tick duration used in the specific
implementation to correctly perform this arithmetic. We will
use “fCTD”, standing for “clock tick duration”, as the name of a
function that converts an integer into that integer number of
clock ticks of the correct granularity. So, for example, in:
eff_end_dt – fCTD(1)
fCTD takes on the value of one clock tick. If the granularity is a
month, as it is in most of the examples in this book, the result will
be to subtract one month from the effective end date. If the gran-
ularity is a millisecond-level timestamp, it will subtract one milli-
second from that date. The fCTD function determines the
granularity for a specific Asserted Versioning database from the
miscellaneous metadata table, shown as Figure 8.7 in Chapter 8.
Different DBMSs use different date formats for date literals.
It is also dependent on the def ault language and the date format
currently set. These formats are shown in Figure 14.14.
Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES 333
We used the USA format in parts of the book, so we will
assume that the default date format in our sample DBMS is the
same.
Different DBMSs use different syntax for date arithmetic. SQL
Server would use something like this:
AND DATEADD(DAY, -1, pol.eff_end_dt) > ‘07/15/2010’
where DAY is the granularity (which can also be abbreviated as
DD or D), while DB2 might use:
AND (pol.eff_end_dt - 1 DAY) > ‘07/15/2010’
with the reserved word DAY indicating the granularity. We will
use the T-SQL format for our examples, and will assume our
clock tick granularity is one month, to keep it in synch with
the examples used in the book. However, in real-world

databases, the granularity would more likely be a day, a second
or a microsecond. This fCTD translation could be built into a
reusable database function as part of the framework based on
metadata.
T
1
[starts] P
1
This is a pair of relationships, one the inverse of the other. In
the non-superscripted relationship, the first time period is the
point in time, i.e. the single clock-tick time period. Figure 14.15
shows this relationship,
and its place in our taxonomy. The two
dashed lines in the illustra tion graphically represent T
1
and P
1
,
with T
1
being the upper dashed line.
The predicate for this relationship, as it holds between a
period of time expressed as
a pair of dates using the closed-open
convention, and a point in time, is:
(T
1
¼ eff_beg_dt)
It says that T
1

starts at P
1
.
Consider the following request for information: which
policies begin on the same date as the 2009 Diabetes Manage-
ment Wellness Program?
Name Layout Example
ISO yyyy-mm-dd 2010-09-25
09/25/2010
25.09.2010
2010-09-25
mm/dd/yyyy
dd.mm.yyyy
yyyy-mm-dd
USA
EUR
JIS
Figure 14.14 Date Formats for Date Literals.
334 Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES
The SQL written to fulfill this request is:
SELECT * FROM V_Allen_Example
WHERE pol_eff_beg_dt ¼ wp_eff_beg_dt
T
1
[finishes] P
1
This is a pair of relationships, one the inverse of the other. In
the non-superscripted relationship, the first time period is the
point in time, i.e. the single clock-tick time period. Figure 14.16
shows this relationship

, and its place in our taxonomy. The two
dashed lines in the illustration graphically represent T
1
and P
1
,
with T
1
being the upper dashed line.
The predicate for this relationship, as it holds between a
period of time exp
ressed as a pair of dates using the closed-open
convention, and a point in time, is:
(T
1
¼ eff_end_dt – fCTD(1))
Since the effective end date of a time period is the next clock
tick after the last clock tick in that time period, this predicate
says that P
1
ends on the clock tick that is T
1
.
Fills
Occupies
Aligns
Starts
|-|
| |
Intersects

Time Period Relationships
Along a Common Timeline
Figure 14.15 T
1
[starts] P
1
.
Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES 335
Consider the following request for information: which
policies began on the same date as the 2009 Diabetes Wellness
Management Program ended?
The SQL written to fulfill this request is:
SELECT * FROM V_Allen_Example
WHERE pol_eff_beg_dt ¼ DATEADD(MONTH, -1,
wp_epis_end_dt)
T
1
[during] P
1
This is the relationship in which a single clock tick occurs
after the start of a period of time, and before that period of time
ends. Figure 14.17 sho
ws this relationship,
and its place in our
taxonomy. The two dashed lines in the illustration graphically
represent T
1
and P
1
,withT

1
being the upper dashed line.
The predicate for this relationship, as it holds between a
period of time expressed as
a pair of dates using the closed-open
convention, and a point in time, is:
(eff_beg_dt < T
1
) AND (eff_end_dt À f CTD(1) > T
1
)
It says that T
1
occurs during P
1
just in case P
1
starts before T
1
and ends after T
1
.
Time Period Relationships
Along a Common Timeline
Intersects
Fills
Occupies
Finishes
Aligns
|-|

| |
Figure 14.16 T
1
[finishes] P
1
.
336 Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES
Consider the following request for information: which
policies began before the 2009 Diabetes Wellness Management
Program started, and ended after it started?
The SQL written to fulfill this request is:
SELECT * FROM V_Allen_Example
WHERE pol_eff_beg_dt < wp_eff_beg_dt
AND DATEADD(MONTH, À1, pol_epis_end_dt) > wp_eff_beg_dt
T
1
[occupies] P
1
This not an Allen relationship. It is the node in our taxonomy
of Allen relationships which, when one of the time periods is a
point in time, includes the [starts], [finishes], and [during]
relationships. In other words, it combines the [during]
relationships with the [
aligns] relationships. These are all the
relationships in which a time period (of more than one clock
tick) includes a point in time.
The predicate for this relationship, as it holds between a
period of time expressed as a pair of dates using the closed-open
convention, and a point in time, is:
(eff_beg_dt <¼ T

1
) AND (eff_end_dt > T
1
)
Time Period Relationships
Along a Common Timeline
Intersects
Fills
Occupies
During
|-|
| |
Figure 14.17 T
1
[during] P
1
.
Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES 337
It says that P
1
occupies T
1
just in case P
1
starts on or before T
1
and ends on or after T
1
.
Consider the following request for information: which

policies began on or before the 2009 Diabetes Management
Wellness Program started, and ended on or after it started?
The SQL written to fulfill this request is:
SELECT * FROM V_Allen_Example
WHERE pol_eff_beg_dt <¼ wp_eff_beg_dt
AND pol_epis_end_dt > wp_eff_beg_dt)
T
1
[before] P
1
The predicate for this relationship, as it holds between a
period of time expressed as a pair of dates using the closed-open
convention, and a point in time, is:
(T
1
þ fCTD(1) < eff_beg_dt)
It says that P
1
starts at least one clock tick after T
1
; similarly T
occurs at least one clock tick before P
1
starts.
The inverse of this relationship is: P
1
[before
À1
]T
1

. In this
superscripted relationship, the time period is later than the point
Time Period Relationships
Along a Common Timeline
Intersects
Fills
Occupies
During
|-|
| |
Finishes
Aligns
Starts
|-|
| |
|-|
| |
Figure 14.18 T
1
[occupies] P
1
.
338 Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES
in time. The predicate for this relationship, as it holds between
two time periods expressed as pairs of dates using the closed-
open convention, is:
(eff_end_dt < T
1
)
It says that P

1
ends at least one clock tick before T
1
.
It follows that to pick out those versions or episodes which
are non-contiguous with a given point in time, the predicate is:
(T
1
þ fCTD(1) < eff_beg_dt) OR (eff_end_dt < T
1
)
Consider the following request for information: which
policies have a temporal gap between when they began and
when the 2009 Diabetes Management Wellness Program ended?
The SQL written to fulfill this request is:
SELECT * FROM V_Allen_Example
WHERE wp_epis_end_dt
< pol_eff_beg_dt
T
1
[meets] P
1
The predicate for this relationship, as it holds between a
period of time expressed as a pair of dates using the closed-open
convention, and a point in time, is:
(T
1
þ fCTD(1) ¼ eff_beg_dt)
It says that P
1

starts immediately after T
1
.
The inverse of this relationship is: P
1
[meets
À1
]T
1
. In this
superscripted relationship, the time period is earlier than the
point in time. The predicate for this relationship, as it holds
Time Period Relationships
Along a Common Timeline
Excludes
Before
|-| | |
Figure 14.19 T
1
[before] P
1
.
Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES 339
between two time periods expressed as pairs of dates using the
closed-open convention, is:
(eff_end_dt ¼ T
1
)
It says that P
1

ends immediately before T
1
.
It follows that to pick out those versions, or episodes which
are contiguous with a given point in time, the predicate is:
(T
1
þ fCTD(1) ¼ eff_beg_dt) OR (eff_end_dt ¼ T
1
)
Consider the following request for information: which
policies began at the same time the 2009 Diabetes Management
Wellness Program ended?
The SQL written to fulfill this request is:
SELECT * FROM V_Allen_Example
WHERE wp_epis_end_dt ¼
pol_eff_beg_dt
P
1
[excludes] T
1
This not an Allen relationship. It is the node in our taxonomy
of Allen relationships which includes the [before], [before
À1
],
[meets] and [meets
À1
] relationships.
The predicate for this relationship is:
(T

1
þ fCTD(1)) <¼ eff_beg_dt) OR (eff_end_dt <¼ T
1
)
It says that P
1
starts after T
1
or ends before T
1
. Note that if a
time period’s effective end date is T
1
, that time period ended
the day before T
1
.
Consider the following request for information: which
policies ended anytim e before the 2009 Diabetes Management
Wellness Program began or started anytime after the 2009 Diabe-
tes Manage ment Wellness Program began? Note that this is not
Time Period Relationships
Along a Common Timeline
Excludes
MeetsMeets
|-| |
Figure 14.20 T
1
[meets] P
1

.
340 Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES
the same as a request for policies which ended [before] the 2009
Diabetes Management Wellness Program began.
The SQL written to fulfill this request is:
SELECT * FROM V_Allen_Example
WHERE (pol_epis_end_dt <¼ wp_eff_beg_dt
OR DATEADD(MONTH, þ1, wp_eff_beg_dt) <¼
pol_eff_beg_dt)
Point in Time to Point in Time Queries
Another special set of Allen relationships consists of the
relationships between two points in time, T
1
and T
2
. There are
only three such relationships. One point in time may precede
another and be non-contiguous with it, or precede the other
and be contiguous with it, or be the same as the other.
T
1
[before] T
2
The predicate for this relationship, as it holds betw een two
points in time, expressed as points in time, is:
((T
1
þ fCTD(1)) < T
2
)

It says that T
1
comes before T
2
, and that there is at least one
clock tick between it and T
2
. Note that because of the require-
ment for this one clock tick gap, the Allen relationship [before]
does not mean the same thing as “before” in ordinary language.
The inverse of this relationship is: T
1
[before
À1
]T
2
. The pred-
icate for this relationship, as it holds between two points in time,
expressed as points in time, is:
((T
1
– fCTD(1)) > T
2
)
It says that T
1
comes after T
2
, and that there is at least one
clock tick between it and T

2
.
Time Period Relationships
Along a Common Timeline
Excludes
Meets
Before
|-| |
|-| | |
Figure 14.21 P
1
[excludes] T
1
.
Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES 341
T
1
[meets] T
2
The predicate for this relationship, as it holds between two
points in time, expressed as points in time, is:
((T
1
þ fCTD(1)) ¼ T
2
)
It says that T
1
comes immediately before T
2

.
The inverse of this relationship is: T
1
[meets
À1
]T
2
. The predi-
cate for this relationship, as it holds between two points in time,
expressed as points in time, is:
((T
1
– fCTD(1)) ¼ T
2
)
It says that T
1
comes immediately after T
2
.
T
1
[equals]T
2
The predicate for this relationship, as it holds between two
points in time, expressed as points in time, is:
(T
1
¼ T
2

)
It says that T
1
and T
2
are equal if and only if they occur on the
same clock tick.
Before
|-| |-|
Excludes
Time Period Relationships
Along a Common Timeline
Figure 14.22 T
1
[before] T
2
.
Meets
Time Period Relationships
Along a Common Timeline
Excludes
Meets
|-|-|
Figure 14.23 T
1
[meets] T
2
.
342 Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES
A Claims Processing Example

With the Asserted Versioning queries developed and
discussed in this book, we have generally chosen to sacrifice
realism in the examples in order to guarantee completeness of
coverage. For example, in Chapter 13 we developed and
discussed SQL views of nine categories of bi-temporal data, eight
of those categories being what we called pipeline datasets. And
yet few real-world collections of bi-temporal data, we acknowl-
edged, would correspond to precisely one of those nine
categories of data (with the one exception of currently asserted
current versions of data, the category whi ch corresponds to the
data in a conventional table). Completeness of coverage was
guaranteed, however, because those nine categories are a math-
ematical partitioning of all possible combinations of past, pres-
ent and future assertion and effective time data.
Thus far in Chapter 14, we have developed and discussed SQL
predicates, and SQL statements illustrating their use, each
corresponding to one of the Allen relationships or one of the
nodes in our taxonomy of Allen relationships. We have reviewed
all possible Allen relationships, and taxonomic groupings of
them, between pairs of time periods, between a time period
and a point in time, and between two points in time. Once again,
completeness of coverage has been guaranteed because of the
use of a mathematical partitioning of all possible types of
queries. But once again, the examples have not been very
realistic.
Time Period Relationships
Along a Common Timeline
Intersects
Fills
Equals

|-|
|-|
Figure 14.24 T
1
[equals]T
2
.
Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES 343
In this section, we move on from completeness to verisimili-
tude. Having used a simplified set of health insurance data
throughout this book, we continue by developing a query about
claims issued against policies held by clients.
Claims data has not been used, so far, and so we begin with
the SQL definition of a simplified Adjudicated Claims table.
CREATE TABLE dbo.Adjud_Claim (
claim_rowid int identity,
policy_oid int null,
claim_amt money null,
service_dt datetime default getdate() null,
adjud_dt datetime null,
row_crt_dt datetime default getdate() not null)
This Adjudicated Claims table is not an asserted version table.
It is an event table, not a persistent object table, and each of its
rows represent an event on which an insura nce claim was
adjudicated. As an event, each claims adjudication has no per-
sistence. It happens, and then it’s over.
In the Adjudicated Claims table, policy_oid is not a foreign
key, because there is no table for which it is the primary key.
The Policy table is an asserted version table, and its primary
key is the combination of policy_oid with assertion begin date

and effective begin date. Nor is policy_oid a temporal foreign
key, because Asserted Versioning does not recognize and manage
referential relationships between non-temporal tables and
asserted version tables. For example, the AVF may temporally
delete a policy after several claims have been adjudicated that
reference that policy, and it may temporally delete those policies
effective at some point in time prior to the adjudication of those
claims. It is the responsibility of the application which manages
the Adjudicated Claims table to make sure it is not adjudicating
claims against terminated policies.
Returning to our query, for each adjudicated claim, it will
show the client number and name, the policy number, type
and copay amount, and the claim service date, amount, and
adjudication date.
For the policy associated with the claim, this query picks out
the version of the policy that was in effect on the service date.
Clearly, we are not interested in any other version of the policy. In
particular, we are not interested in the version of the policy that
is in effect when the query is run, or in the version of the policy
that was in effect on the adjudication date. Those versions may
in fact be correct, but we can’t count on it. We want to see the ver-
sion that was in effect at the time the medical service was rendered.
344 Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES
But there is more to picking out the correct policy data than
this. We need, not simply the correct version, but the appropri-
ate assertion of that version. For it is possible that, between the
service date and the adjudication date or after the adjudication
date, we found an error in the data about the policy as it was
on the service date.
Say that the service date is 9/24/2009 and the adjudication date

is 12/07/2009, and that between those two dates, we discovered
and corrected an error in the version of the policy that was in effect
on 9/24/2009, and then again after the adjudication another cor-
rection was made to the policy for the effective period of the ser-
vice. This means that we have three rows representing the same
version of the same policy, both purporting to describe the policy
as it was on 9/24/2009. Which one do we want?
We want the one that was used to adjudicate the claim, of
course, that being the assertion that was current on the adjudica-
tion date, 12/07 /2009. So we want policy data that was effective
on the service date, and asserted as of the adjudication date
whether it was right or wrong because we want to see the source
of the Explanation of Benefits (EOB) as the customer sees it.
This query also returns the client number and name of the
client that owns the policy. Like most persistent object data, of
course, that name may have changed over time. In this sample
query, we choose to use the client data that was currently
asserted at the time that the row in the Adjudicated Claims table
was physically created.
Finally, we include one selection criterion on the query. We
select those adjudicated claims where the claim amount is
greater than the client’s copay amount. These, of course, are
the claims for which a payment will be made by the insurance
company to the servic e provider.
Here is the query we have described.
SELECT c.client_nbr, c.client_nm,
p.policy_nbr, p.policy_type, p.copay_amt,
cl.service_dt, cl.claim_amt, cl.adjud_dt
FROM Claim cl
INNER JOIN Policy_AV p

ON p.policy_oid ¼ cl.policy_oid
AND p.eff_beg_dt <¼ cl.service_dt
AND p.eff_end_dt > cl.service_dt
AND p.asr_beg_dt <¼ cl.adjud_dt
AND p.asr_end_dt > cl.adjud_dt
INNER JOIN Client_AV c
ON c.client_oid ¼ p.client_oid
Chapter 14 ALLEN RELATIONSHIP AND OTHER QUERIES 345