SQL表插入新记录时校验指定名称的日期范围冲突需求

Alright, let's figure out how to check for date range conflicts when inserting new records into your Product table. Here's a step-by-step solution:

核心逻辑：判断日期范围是否重叠 #

First, let's break down when two date ranges conflict. For an existing record's range [FromDate, ToDate] and a new record's range [NewFrom, NewTo], they overlap if any of these scenarios are true:

• The new range fully contains the existing one: NewFrom <= FromDate AND ToDate <= NewTo
• The new range is fully contained by the existing one: FromDate <= NewFrom AND NewTo <= ToDate
• The new range's start falls inside the existing range: FromDate <= NewFrom <= ToDate
• The new range's end falls inside the existing range: FromDate <= NewTo <= ToDate

Luckily, all these scenarios can be condensed into a single, concise condition:

FromDate <= NewTo AND NewFrom <= ToDate

This one line covers every possible overlap case. If your business considers adjacent dates (e.g., existing ToDate is 10-Jan, new NewFrom is 11-Jan) as non-conflicting, just replace the <= with < in the condition.

SQL Query to Detect Conflicts #

Assume you have three parameters for the new record:

• @NewName: The name of the product you're inserting
• @NewFromDate: The start date of the new record
• @NewToDate: The end date of the new record

Use this query to find any conflicting existing records:

SELECT ProductId
FROM Product
WHERE Name = @NewName
  AND FromDate <= @NewToDate
  AND @NewFromDate <= ToDate;

How to Use This Query #

• If the query returns one or more rows: There's a conflict. Return the corresponding ProductId(s) to indicate which existing records are overlapping.
• If the query returns no rows: No conflicts exist. You can safely insert the new record, and return false (or whatever non-conflict indicator your system uses).

Example Walkthrough #

Let's use your sample data to test:
Existing record: ProductId=2, Name=B, FromDate=5-Feb-2017, ToDate=5-Feb-2017
New record attempt: Name=B, FromDate=5-Jan-2017, ToDate=15-Jan-2017

In this case, the dates don't overlap (Jan vs Feb), so the query returns nothing—you can insert the new record. If the existing record was FromDate=5-Jan-2017, ToDate=10-Jan-2017 instead, the query would return ProductId=2, signaling a conflict.

Performance Optimization #

To speed up this check (especially as your table grows), add a composite index on the Name, FromDate, and ToDate columns:

CREATE INDEX IX_Product_Name_Dates ON Product (Name, FromDate, ToDate);

This index will help the database quickly locate matching records without scanning the entire table.

内容的提问来源于stack exchange，提问作者Mayank