T-SQL How to Check Overlapping Date Ranges: User Subscription Example

To check for overlapping date ranges in T-SQL, you can use a query that compares the start and end dates of the date ranges and looks for any intersections. In this post, we will walk through how to check for overlapping date ranges using a user subscription example.

Assuming you have a table named "UserSubscriptions" with columns "UserID", "StartDate", and "EndDate" with a few sample records populated, let's try to come up with SQL queries find overlapping date ranges for user subscriptions.

You can use this Fiddle to follow along and practice checking date range overlaps.

Table structure of Subscriptions table:

Create 'Subscriptions' table

CREATE TABLE Subscriptions (
    SubscriptionID INT PRIMARY KEY,
    UserID INT,
    SubscriptionStart DATE,
    SubscriptionEnd DATE
);

For the demo purpose, we have subscriptions for three users (UserID 101, 102, and 103) with varying start and end dates.

Insert demo data into the 'Subscriptions' table

INSERT INTO Subscriptions (SubscriptionID, UserID, SubscriptionStart, SubscriptionEnd)
VALUES
    (1, 101, '2023-01-01', '2023-03-31'),
    (2, 101, '2023-04-15', '2023-06-30'),
    (3, 102, '2023-02-01', '2023-04-30'),
    (4, 103, '2023-03-10', '2023-05-10'),
    (5, 101, '2023-06-01', '2023-08-31'),
    (6, 102, '2023-04-01', '2023-06-15');

Subscriptions Table:

SubscriptionID	UserID	SubscriptionStart	SubscriptionEnd
1	101	2023-01-01	2023-03-31
2	101	2023-04-15	2023-06-30
3	102	2023-02-01	2023-04-30
4	103	2023-03-10	2023-05-10
5	101	2023-06-01	2023-08-31
6	102	2023-04-01	2023-06-15

Find Different Users' Subscriptions that Overlap

The following SQL query is used to find and display overlapping date ranges within the "Subscriptions" table. It identifies pairs of subscriptions belonging to different users that have overlapping date ranges.

SQL to find pairs of subscriptions belonging to different users that have overlapping date ranges

SELECT s1.UserID
	,s1.SubscriptionID AS SubID
	,s1.SubscriptionStart AS SubStart
	,s1.SubscriptionEnd AS SubEnd
	,s2.UserID AS OverlapUserID
	,s2.SubscriptionID AS OverlapSubID
	,s2.SubscriptionStart AS OverlapSubStart
	,s2.SubscriptionEnd AS OverlapSubEnd
FROM Subscriptions s1
JOIN Subscriptions s2 ON s1.UserID <> s2.UserID
	AND s1.SubscriptionID < s2.SubscriptionID
	AND s1.SubscriptionStart <= s2.SubscriptionEnd
	AND s1.SubscriptionEnd >= s2.SubscriptionStart
ORDER BY s1.UserID
	,s1.SubscriptionID;

Query Results: Different Users' Subscriptions that Overlap

UserID	SubID	SubStart	SubEnd	OverlapUserID	OverlapSubID	OverlapSubStart	OverlapSubEnd
101	1	2023-01-01	2023-03-31	102	3	2023-02-01	2023-04-30
101	1	2023-01-01	2023-03-31	103	4	2023-03-10	2023-05-10
101	2	2023-04-15	2023-06-30	102	3	2023-02-01	2023-04-30
101	2	2023-04-15	2023-06-30	103	4	2023-03-10	2023-05-10
101	2	2023-04-15	2023-06-30	102	6	2023-04-01	2023-06-15
101	5	2023-06-01	2023-08-31	102	6	2023-04-01	2023-06-15
102	3	2023-02-01	2023-04-30	103	4	2023-03-10	2023-05-10
103	4	2023-03-10	2023-05-10	102	6	2023-04-01	2023-06-15

Here's an explanation of the query step by step:

1. The SELECT statement specifies the columns to be included in the result set. The selected columns provide information about both the original subscription (s1) and the overlapping subscription (s2):

   • s1.UserID: User ID of the first subscription.
   • s1.SubscriptionID AS SubID: Subscription ID of the first subscription with an alias "SubID."
   • s1.SubscriptionStart AS SubStart: Start date of the first subscription with an alias "SubStart."
   • s1.SubscriptionEnd AS SubEnd: End date of the first subscription with an alias "SubEnd."
   • s2.UserID AS OverlapUserID: User ID of the overlapping subscription.
   • s2.SubscriptionID AS OverlapSubID: Subscription ID of the overlapping subscription with an alias "OverlapSubID."
   • s2.SubscriptionStart AS OverlapSubStart: Start date of the overlapping subscription with an alias "OverlapSubStart."
   • s2.SubscriptionEnd AS OverlapSubEnd: End date of the overlapping subscription with an alias "OverlapSubEnd."

2. The query performs a self-join on the "Subscriptions" table using two aliases, s1 and s2.

3. The join condition is specified in the ON clause, and it includes multiple conditions:

   • s1.UserID <> s2.UserID: This condition ensures that we are comparing subscriptions belonging to different users, preventing self-comparisons.
   • s1.SubscriptionID < s2.SubscriptionID: This condition ensures that we are comparing different subscriptions of different users.
   • s1.SubscriptionStart <= s2.SubscriptionEnd: This condition checks if the start date of the first subscription is less than or equal to the end date of the overlapping subscription.
   • s1.SubscriptionEnd >= s2.SubscriptionStart: This condition checks if the end date of the first subscription is greater than or equal to the start date of the overlapping subscription.

These conditions work together to identify pairs of subscriptions belonging to different users that have overlapping date ranges.

4. The ORDER BY clause at the end of the query specifies the ordering of the result set. It orders the results first by the user ID of the original subscription (s1.UserID) and then by the subscription ID of the original subscription (s1.SubscriptionID).

The query returns pairs of subscriptions from different users that have overlapping date ranges, with detailed information about each subscription and the user IDs involved. The result set is ordered to make it easier to analyze and identify overlapping date ranges.

Identify Pairs of Subscriptions for the Same User that Overlap

The following SQL query is designed to find and display overlapping date ranges within the "Subscriptions" table. It identifies pairs of subscriptions belonging to the same user that have overlapping date ranges.

SQL to check pairs of subscriptions belonging to the same user that have overlapping date ranges

SELECT s1.UserID
	,s1.SubscriptionID AS SubID
	,s1.SubscriptionStart AS SubStart
	,s1.SubscriptionEnd AS SubEnd
	,s2.UserID AS OverlapUserID
	,s2.SubscriptionID AS OverlapSubID
	,s2.SubscriptionStart AS OverlapSubStart
	,s2.SubscriptionEnd AS OverlapSubEnd
FROM Subscriptions s1
JOIN Subscriptions s2 ON s1.UserID = s2.UserID
	AND s1.SubscriptionID < s2.SubscriptionID
	AND s1.SubscriptionStart <= s2.SubscriptionEnd
	AND s1.SubscriptionEnd >= s2.SubscriptionStart
ORDER BY s1.UserID
	,s1.SubscriptionID;

Query Results: Same User's Subscriptions that Overlap

UserID	SubID	SubStart	SubEnd	OverlapUserID	OverlapSubID	OverlapSubStart	OverlapSubEnd
101	2	2023-04-15	2023-06-30	101	5	2023-06-01	2023-08-31
102	3	2023-02-01	2023-04-30	102	6	2023-04-01	2023-06-15

Here's an explanation of the query step by step:

1. The SELECT statement specifies the columns to be included in the result set. The selected columns provide information about both the original subscription (s1) and the overlapping subscription (s2):

   • s1.UserID: User ID of the first subscription.
   • s1.SubscriptionID AS SubID: Subscription ID of the first subscription with an alias "SubID."
   • s1.SubscriptionStart AS SubStart: Start date of the first subscription with an alias "SubStart."
   • s1.SubscriptionEnd AS SubEnd: End date of the first subscription with an alias "SubEnd."
   • s2.UserID AS OverlapUserID: User ID of the overlapping subscription.
   • s2.SubscriptionID AS OverlapSubID: Subscription ID of the overlapping subscription with an alias "OverlapSubID."
   • s2.SubscriptionStart AS OverlapSubStart: Start date of the overlapping subscription with an alias "OverlapSubStart."
   • s2.SubscriptionEnd AS OverlapSubEnd: End date of the overlapping subscription with an alias "OverlapSubEnd."

2. The query performs a self-join on the "Subscriptions" table using two aliases, s1 and s2.

3. The join condition is specified in the ON clause, and it includes multiple conditions:

   • s1.UserID = s2.UserID: This condition ensures that we are comparing subscriptions belonging to the same user, effectively finding overlaps within a single user's subscriptions.
   • s1.SubscriptionID < s2.SubscriptionID: This condition ensures that we are comparing different subscriptions of the same user.
   • s1.SubscriptionStart <= s2.SubscriptionEnd: This condition checks if the start date of the first subscription is less than or equal to the end date of the overlapping subscription.
   • s1.SubscriptionEnd >= s2.SubscriptionStart: This condition checks if the end date of the first subscription is greater than or equal to the start date of the overlapping subscription.

These conditions work together to identify pairs of subscriptions belonging to the same user that have overlapping date ranges.

4. The ORDER BY clause at the end of the query specifies the ordering of the result set. It orders the results first by the user ID of the original subscription (s1.UserID) and then by the subscription ID of the original subscription (s1.SubscriptionID).

The query returns pairs of subscriptions from the same user that have overlapping date ranges, with detailed information about each subscription and the user IDs involved. The result set is ordered to make it easier to analyze and identify overlapping date ranges within the same user's subscriptions.

Conclusion

In summary, both of the provided SQL queries in this post are valuable tools for identifying and displaying overlapping date ranges within a dataset of user subscriptions, but they serve slightly different purposes:

1. First Query (Self-Join on Different Users):

   The first query is designed to identify and display overlapping date ranges between subscriptions of different users within the "Subscriptions" table. It focuses on scenarios where you want to detect overlaps across different users' subscriptions. The query successfully achieves this by using a self-join and a set of well-defined conditions. It provides a clear and organized result set, ordered by user and subscription IDs, making it convenient for reviewing and analyzing overlapping date ranges across different users.

2. Second Query (Self-Join on the Same User):

   The second query, on the other hand, is intended for identifying and displaying overlapping date ranges within subscriptions of the same user within the "Subscriptions" table. This query is suitable when you need to identify overlaps exclusively within a single user's subscriptions. Similar to the first query, it utilizes a self-join and a set of conditions to pinpoint overlapping date ranges, and it also provides a clear and organized result set, ordered by user and subscription IDs.

Both queries showcase the power and flexibility of SQL in efficiently managing and extracting meaningful insights from complex datasets. The choice between them depends on the specific analysis goals and scenarios, either focusing on overlaps between different users or within the subscriptions of the same user. These queries can be valuable tools for tasks such as conflict resolution, resource allocation, or pattern analysis in subscription data, offering a versatile approach to handling overlapping date ranges.

Further Reading

You can check out different ways of writing SQL to detect overlapping date range in this detailed post Detecting Date Range Overlaps in SQL Tables: A Practical Guide with T-SQL Examples.