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:
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:
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:
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:
Just make sure you have the compatibility level set to 160 or it will return an error and not recognize the new feature.
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:
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.