Defining Ranges
A simple way to define a range in a table is to using a pair of columns. The first column holds the start of the range and the second column holds the end. This manner of defining range boundaries works equally well for numeric or date and time ranges. If you want to execute a query that determines in which range a particular value lies, it might be important that none of the ranges overlap. If they did, that query might return multiple rows.
As an example, consider the following table. Here we are defining the rules for compound interest payments on a savings account. This type of account pays a different interest rate depending upon the balance of the account. Five ranges are defined, each with an attached rate.
Balance From | Balance To | Interest Rate |
---|
£0.00 | £999.99 | 0.50% |
£1,000 | £1,999.99 | 0.75% |
£2,000 | £2,999,99 | 1.00% |
£3,000 | £4,999.99 | 1.25% |
£5,000 | £9,999.99 | 1.50% |
If the table that defined the above rules permitted overlapping or duplicated balance ranges, it would be possible for an account's balance to fall within two or more ranges. This might lead to the incorrect amount of annual interest being applied.
Preventing a duplicate range is easy to achieve. All that you need to do is apply unique indexes, unique constraints or a primary key to the "From" and "To" columns. However, these constraints cannot prevent ranges from overlapping where neither the from or to values collide. For this type of restriction we can use a trigger.
To demonstrate, we need a table with some data. In a new, test database run the following script. This recreates the interest rate rules from the above table. If you were to try to add extra rows that included overlapping ranges, these rows would be permitted.
CREATE TABLE InterestRates
(
BalanceFrom MONEY,
BalanceTo MONEY,
InterestRate NUMERIC(4,2),
PRIMARY KEY (BalanceFrom, BalanceTo)
)
INSERT INTO InterestRates VALUES (0, 999.99, 0.5)
INSERT INTO InterestRates VALUES (1000, 1999.99, 0.75)
INSERT INTO InterestRates VALUES (2000, 2999.99, 1)
INSERT INTO InterestRates VALUES (3000, 4999.99, 1.25)
INSERT INTO InterestRates VALUES (5000, 9999.99, 1.5)
Creating the Trigger
The trigger that is required to prevent overlapping is quite simple. It has to perform a few basic actions. Firstly, the trigger must execute a query that finds any range that the inserted row collides with. This requires the inserted or updated row and the existing rows to be compared. Any existing rows where the BalanceFrom column has a lower or equal value to the new BalanceTo, and the BalanceTo value is greater than the new BalanceFrom are potential collisions. The only exception is when an existing row is updated and collides with itself. These updates must be disregarded.
If any rows are returned by the trigger's query, an error must be raised and the current transaction rolled back. To check for this you can use the EXISTS function around the query, and RAISERROR to notify of the problem.
To create the trigger, run the following script:
CREATE TRIGGER NoOverlappingRanges ON InterestRates FOR INSERT, UPDATE AS
BEGIN
IF EXISTS(
SELECT
1
FROM
InterestRates R
INNER JOIN
inserted I
ON (
(R.BalanceFrom <= I.BalanceTo AND I.BalanceFrom <= R.BalanceTo)
AND
NOT (R.BalanceFrom = I.BalanceFrom AND I.BalanceTo = R.BalanceTo)))
BEGIN
RAISERROR('Balance ranges may not overlap', 16, 1)
ROLLBACK
END
END
The combination of the primary key and the trigger now prevent any of the following INSERT or UPDATE statements:
INSERT INTO InterestRates VALUES (1000, 1999.99, 2)
INSERT INTO InterestRates VALUES (-1, 1000, 2)
INSERT INTO InterestRates VALUES (-1, 1, 2)
INSERT INTO InterestRates VALUES (9500, 10050, 2)
INSERT INTO InterestRates VALUES (9500, 9800, 2)
UPDATE InterestRates SET BalanceFrom = 1050, BalanceTo = 1099.99 WHERE BalanceFrom = 5000
NB: To complete the table you should add a check constraint to ensure that it is not possible to create an invalid range, where the BalanceFrom is greater than the BalanceTo value.
ALTER TABLE InterestRates
ADD CONSTRAINT CK_InterestRateRangeValid
CHECK (BalanceFrom <= BalanceTo)
15 January 2014