ALLEN RELATIONSHIP AND OTHER QUERIES - Managing Time in Relational Databases - page 312

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_epis_end_dt

P 1 [ aligns ] P 2
This not an Allen relationship. It is the node in our taxonomy
of Allen relationships which includes the [starts], [starts 1 ],

[finishes] and [finishes 1 ] relationships.
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_end_dt 2 ))
OR ((eff_beg_dt 1 > eff_beg_dt 2 ) AND (eff_end_dt 1 ¼
eff_end_dt 2 ))
AND NOT((eff_beg_dt 1 ¼ eff_beg_dt 2 ) AND (eff_end_dt 1 ¼
eff_end_dt 2 ))

Time Period Relationships
Along a Common Timeline

Intersects

Fills

Occupies

Aligns

Starts Finishes

|-----|
|------------|

|-----|
|-----------|

Figure 14.4 P 1 [aligns] P 2 .