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

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
AND NOT (pol_eff_beg_dt ¼ wp_eff_beg_dt
AND pol_epis_end_dt ¼ wp_epis_end_dt)

P 1 [ ]P 2

This is a single relationship, and has no inverse.
The predicate for this relationship, as it holds between two
time periods expressed as pairs of dates using the closed-open
convention, is:

equals

(eff_beg_dt 1 ¼ eff_beg_dt 2 )
AND (eff_end_dt 1 ¼ eff_end_dt 2 )

It says that P 1 and P 2 both start and end at the same time.
Consider the following request for information: which
policies began when the Diabetes Management Wellness Pro-
gram for 2009 began, and ended when that program ended?
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

¼

Time Period Relationships
Along a Common Timeline

Intersects

Fills

Equals
|-----|
|-----|