Databases Reference
In-Depth Information
AND c.eff_beg_dt
cl.row_crt_dt
<¼
AND c.eff_end_dt
cl.row_crt_dt
>
AND c.asr_beg_dt
cl.row_crt_dt
<¼
cl.row_crt_dt
WHERE cl.claim_amt
>
p.copay_amt
ORDER BY cl.adjud_dt, c.client_nbr, p.policy_nbr,
p.eff_beg_dt;
AND c.asr_end_dt
>
To conclude this section, we show what this query might look
like if the SQL language supported PERIOD datatypes, and also
our taxonomy of Allen relationships. We suppose that the taxon-
omy node [fills
1
] is represented by the reserved word INCLUDES.
With a SQL language like this, the Asserted Versioning schema no
longer has pairs of dates to represent its two time periods. Instead,
it has the single columns asr_per and eff_per.
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_per INCLUDES cl.service_dt
AND p.asr_per INCLUDES cl.adjud_dt
INNER JOIN Client_AV c
ON c.client_oid
¼
p.client_oid
AND c.eff_per INCLUDES cl.row_crt_dt
AND c.asr_per INCLUDES cl.row_crt_dt
WHERE cl.claim_amt
>
p.copay_amt
ORDER BY cl.adjud_dt, c.client_nbr, p.policy_nbr,
p.eff_beg_dt;
¼
In either form, what is striking about the query is its simplicity
relative to the complexity of the bi-temporal semantics that under-
lies it. Unlike queries in the standard temporal model and, for that
matter, uni-temporal queries in the alternative temporal model as
well, this query does not assemble a collection of rows and then
proceed to check for temporal gaps and temporal overlaps within
sub-selected collections of those rows. Asserted Versioning
enforces bi-temporal semantics once, as the data is being created
and modified, rather than each time the data is queried.
In Other Words
With appropriate temporal extensions to the SQL language,
the expression of all thirteen Allen relationships, and of this
and other
relationships which are combinations of
those
