Prevent Overlapping Times

Each record shows a period of cover for a particular insurance policy, the amount of premium that was paid to cover that period, and the value insured over that period.

Sample data follows (dates are in mm/dd/yyyy format):

The sample data shows premium information for two policies. The first policy has three continuous periods of cover. The second policy has just one period of cover.

According to my business rules, the following entry is incorrect, and should be rejected by the system:

This entry is incorrect because the period of cover overlaps another period of cover for the same policy.  The following piece of SQL will determine whether any overlapping periods of cover have been entered into the database:

SELECT t1.lPremiumID, t1.lPolicyID, t1.wStart, t1.wFinish,
t2.lPremiumID, t2.wStart, t2.wFinish
FROM tblPremium AS t1 INNER JOIN tblPremium AS t2
ON t1.PolicyID=t2.PolicyID
WHERE t1.wStart<=t2.wFinish AND t1.wFinish>=t2.wStart

That's all very well, but how can I stop overlapping records from creeping into my database in the first place?

There is a simple solution which will guarantee that no overlapping records are entered into the database.  Modify tblPremium by removing the wFinish field.

The data in tblPremium would then need to be modified as follows:

This table now contains a record for each period of time for which a policy-holder is not covered by insurance.

Using this structure requires a few changes in program logic.  For example, using the previous structure, the due date for a policy-holder's next premium payment could be calculated by finding the maximum wFinish for that policy:

SELECT MAX(wFinish) FROM tblPremium
WHERE lPolicyID = @SelectedPolicyID

Using the new structure, the same date would be returned by

SELECT MAX(wStart-1) FROM tblPremium
WHERE lPolicyID = @SelectedPolicyID AND cInsuredAmount=0

In this example, I have taken advantage of implicit date conversion, to subtract one day from the start date of the last non-insured period.

Note that I cannot just look for a record with cInsuredAmount=0 because, if a policy lapses (eg through non-payment of the premium), and is then reinstated, there will be a record with cInsuredAmount=0 for the period between the lapse and the reinstatement, and another for a date in the future when the policy is due to expire.

Adelle.