All other questions and queries on this topic seem to be assuming all or some of the datetimes involved are non-null. I haven't seen a solution for when all of the datetimes are possibly NULL.

In my use-case, I have promotions for products which have a start and end date.

• A start date of NULL means the promotion is always active, as long as the end date hasn't passed.
• An end date of NULL means the promotion lasts forever, and is active assuming the start date has passed.
• A promotion with both NULL means the promotion is always active and will overlap with every other promotion possible for that product.

I want to give the client a warning when they try to add another promotion for a product that overlaps with an existing promotion.

So, we have four datetime2, StartA, EndA and StartB, EndB, where none, some or all of these may be NULL.

How can I write a SELECT statement that returns all promotions which overlap?

If relevant I'm using SQL Server, and here is a SQLFiddle if it helps.

CodePudding user response：

I'm going to assume the following:

• "Returns all promotions which overlap" = "Returns all promotions which are active"
• Table is called "promotions".
• Relative columns on the table are "StartDate" and "EndDate"

With those in mind, it's relatively simple:

SELECT * FROM promotions
WHERE
( StartDate IS NULL AND EndDate<=GETDATE() ) OR  -- A start date of NULL means the promotion is always active, as long as the end date hasn't passed
( EndDate IS NULL And StartDate>=GETDATE() ) OR -- An end date of NULL means the promotion lasts forever, and is active assuming the start date has passed.
( StartDate IS NULL AND EndDate IS NULL ) -- A promotion with both NULL means the promotion is always active 

CodePudding user response：

This can be achieved using self join as shown below.

Note : This will be a little costly for a very huge table.

---Creating table
---PromotionID is Unique. productID is product for which promotion is created    

create table product_promotion(promotion_id int, product_id int, startdate datetime, EndDate datetime)

--Inserting Sample Data
--For product id 101 and 102 there are overlapping promotions based on the condition described in quetion

Insert into product_promotion
values(1,101,'2022-10-01','2022-10-10'),
(2,101,'2022-10-11',null),
(3,101,null,'2022-11-30'),
(4,102,null,null),
(5,102,'2022-11-01',null),
(6,103,'2022-11-01','2022-11-10'),
(7,103,'2022-11-11','2022-11-15')

--Query to find overlapping results.

select a.product_id,a.promotion_id ,b.promotion_id Overlapped_PromotionId,  
a.startdate, a.EndDate,
 b.startdate Overlapped_StartDate, a.EndDate Overlapped_EndDate
from product_promotion a
join product_promotion b on a.product_id = b.product_id and a.promotion_id != b.promotion_id
where (a.startdate is null and a.EndDate is null)
or (a.startdate is null and b.startdate < a.EndDate) 
or (a.EndDate is null and b.EndDate > a.EndDate)

--Query Results

product_id  promotion_id Overlapped_PromotionId startdate               EndDate                 Overlapped_StartDate    Overlapped_EndDate
----------- ------------ ---------------------- ----------------------- ----------------------- ----------------------- -----------------------
101         3            1                      NULL                    2022-11-30 00:00:00.000 2022-10-01 00:00:00.000 2022-11-30 00:00:00.000
101         3            2                      NULL                    2022-11-30 00:00:00.000 2022-10-11 00:00:00.000 2022-11-30 00:00:00.000
102         4            5                      NULL                    NULL                    2022-11-01 00:00:00.000 NULL

(3 rows affected)