Michelle Michelle - 1 year ago 82
SQL Question

Return Rows with Overlapping Dates

I'm trying to find ONLY those rows that have overlapping dates with other rows.


Period: 13, Start Date: 9/1/2015, End Date: 9/30/2015

Period: 14, Start Date: 9/15/2015, End Date: 10/30/2015

Period: 15, Start Date: 11/1/2015, End Date: 11/30/2015

Here's my current query:

select pd1.periodNumber AS 'Period Seq A', pd1.startDate, pd1.endDate,
pd2.periodNumber AS 'Period Seq B', pd2.startDate, pd2.endDate,
from dbo.PeriodDefinition pd1
inner join dbo.PeriodDefinition pd2 on pd2.startDate > pd1.startDate and
pd2.startDate < pd2.endDate
where (pd1.adjustment =1 AND pd2.adjustment = 1)

It should return only 1 row, and display the data from period 13 (Period A) and period 14 (Period B.) Right now it's returning 3 rows, and displaying 13 and 15 in the result. What am I missing? Thank you!

Answer Source
and  pd2.startDate < pd2.endDate

should be

and pd2.startDate < pd1.endDate  

I would write

on pd2.startDate between pd1.startDate and pd1.endDate

you also probably want a

and pd1.periodNumber != pd2.periodNumber

(assuming periodNumber is the unique row identifier.)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download