Avoiding Overlap in Membership or Subscription Records

Avoid Overlapping Ranges

Memberships or subscriptions have a start and an end. If you are tracking individual renewals separately in a memberships table, as an example, it is important that the start and end dates do not overlap from one term to another.

It is also important that the start and end for the range is never reversed, meaning the start will always be less than or equal to the end of the range. This is easy to enforce at the row level by defining a check constraint in the table definition simply as, in the case of a subscriptions table:

ALTER TABLE dbo.tblSubscriptions ADD CONSTRAINT chk_Start_End_Issue CHECK (StartIssue <= EndIssue)

Avoiding overlapping ranges from one membership or subscription period to another is more difficult to control, as it is an issue across multiple records. While this cannot be as easily prevented as the start always being before or on the end of the period, checking is easy using the windows functions in T-SQL like this:

SELECT MbrshpID, MbrId, LAG(MbrEnds) OVER(Partition By MbrID Order by MbrEnds) as PreviousEnd, MbrStarts, MbrEnds, LEAD(MbrStarts) OVER(Partition By MbrID Order by MbrEnds) as NextStart FROM dbo.tblMemberships

The LAG and LEAD options grab the value from previous or next record in the order specified based on the partition named in the OVER clause, which is said to create a separate window into the data stream from the main SELECT command. This LAG function in the SELECT statement will bring the previous member start date and LEAD function will bring in the next member start date into the individual row of the result set for comparisons. Returning only those records where overlaps occur can be done using a common table equation with a comparison operator as follows:

WITH cteGet AS (
SELECT MbrshpID, MbrId, LAG(MbrEnds) OVER(Partition By MbrID Order by MbrEnds) as PreviousEnd, MbrStarts, MbrEnds, LEAD(MbrStarts) OVER(Partition By MbrID Order by MbrEnds) as NextStart FROM dbo.tblMemberships
)
SELECT MbrshpID,MbrID, MbrStarts, MbrEnds, NextStart FROM cteGet WHERE PreviousEnd >= MbrStarts or NextStart <= MbrEnds order by MbrID, MbrEnds

The WHERE clause of the final SELECT checks to see if the previous member expires (end) date is greater than the current start date or if the next member starts date is less than the current member expires (end) date. If either is true the record is returned and you know there is overlapping membership records. This example is for memberships which have a start and end date, but the very same logic could be used for subscriptions with start and end issue numbers, such as we use in the MA Circulation Manager.

As a special note, the latest versions of SQL Server 2022 and Azure have added a new feature to define the window once, instead of each time an OVER clause is used, thus improving on efficiencies of the query as follows:

SELECT MbrshpID, MbrId, LAG(MbrEnds) OVER wdw AS PreviousEnd, MbrStarts, MbrEnds, LEAD(MbrStarts) OVER wdw as NextStart FROM dbo.tblMemberships WINDOW wdw AS (Partition By MbrID Order by MbrEnds)

Just make sure you have the compatibility level set to 160 or it will return an error and not recognize the new feature.

But

While the LAG and LEAD functions assist in identifying where problems are, the better option is to make sure that overlapping ranges are never added to the database to start with. As mentioned, a CHECK CONSTRAINT will not do this for you, so an alternative is to use a TRIGGER to ensure that dates being added for an individual member or subscriber do not overlap with other records for that individual. Here’s the example to be discussed:

CREATE TRIGGER tgrNoOverlaps ON dbo.tblMemberships FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS (
SELECT 1 FROM dbo.tblMemberships as m1
INNER JOIN inserted as i1 ON m1.MbrshpID <> i1.MbrshpID
WHERE m1.MbrID = i1.MbrID AND
(i1.MbrStarts BETWEEN m1.MbrStarts AND m1.MbrEnds OR
i1.MbrEnds BETWEEN m1.MbrStarts AND m1.MbrEnds)
)
THROW 51100, 'Membership start and end ranges cannot overlap exiting records!',1;
END;

It is a simple SELECT command joining the existing database Memberships table with the trigger’s INSERTED table that will have the row or rows of data being inserted or updated in the table. The only trick here is that, instead of the normal equal operator (=) in the ON clause, a not equal operator (<>) is used because we want to check records other than the ones being inserted or updated. If the records were not excluded, then the EXISTS would always return true and no data could be inserted or updated.

If any overlapping records are found, the THROW command is executed, which raises an error and prevents any records from being inserted or updated. While 90 per cent of the time only a single record is being added or updated, when more than one is in play the trigger will reject the complete update set if any record is invalid.