CODEWITHSUNDEEP
sqlt-sqldate-range
Charles Bernardes
Charles Bernardes

Reputation: 313

Find Overlapping Dates and Return Overlapping Records

What I need is to return all records that may overlap each other.

-- Create Temp Table
CREATE TABLE #Overlap

(SubType varchar(50), 
Cause varchar(9),
CircuitID varchar(100),
BegDate date,
EndDate date,
AmtSought decimal(11,2),
Remarks varchar(max))

--Insert records

INSERT INTO #Overlap VALUES('Original','201500018','36/KQ--/831670/IP /NUVX/','2016-12-01','2016-12-31',354.41,'Rec 1')
INSERT INTO #Overlap VALUES('Original','201500009','36/VCID/826061/IP/NUVX','2016-08-11','2016-08-12',200.50,'Rec 2')
INSERT INTO #Overlap VALUES('Original','201500018','36/KQ--/831670/IP /NUVX/','2016-11-15','2016-12-14',100.25,'Rec 3')
INSERT INTO #Overlap VALUES('Original','201500018','36/KQ--/831670/IP /NUVX/','2016-12-16','2017-01-15',300.75,'Rec 4')
INSERT INTO #Overlap VALUES('Original','201500018','36/KQ--/831670/IP /NUVX/','2017-01-01','2017-01-01',500.00,'Rec 5')
INSERT INTO #Overlap VALUES('Original','201500009','36/VCID/826061/IP/NUVX','2016-07-01','2016-07-31',100.50,'Rec 6')

My result would look like:

SubType   Cause     CircuitID                  BegDate    EndDate    AmtSought Remarks
------------------- -------------------------- ---------- ---------- --------- --------
Original  201500018 36/KQ--/831670/IP /NUVX/   2016-11-15 2016-12-14 100.25    Rec 3
Original  201500018 36/KQ--/831670/IP /NUVX/   2016-12-01 2016-12-31 354.41    Rec 1
Original  201500018 36/KQ--/831670/IP /NUVX/   2016-12-16 2017-01-15 300.75    Rec 4
Original  201500018 36/KQ--/831670/IP /NUVX/   2017-01-01 2017-01-01 500.00    Rec 5

I have tried this from a sample code I saw but it is not returning the desired result. 

Select * from #Overlap a
Inner Join #Overlap b 
on a.SubType = b.SubType 
And a.Cause = b.Cause 
And a.CircuitID = b.CircuitID 
And b.BegDate between a.BegDate and a.endDate 
And b.BegDate < a.endDate

Upvotes: 1

Views: 9619

Answers (2)

TriV
TriV

Reputation: 5148

Assuming that your #Overlap table has a primary key (or unique key) TableId

You could use this following query

SELECT * FROM #Overlap o
WHERE EXISTS
(
    SELECT 1 FROM #Overlap o2
    WHERE o2.SubType = o.SubType
    AND o2.Cause = o.Cause
    AND o2.CircuitID = o.CircuitID
    AND (
            o2.BegDate BETWEEN o.BegDate AND o.EndDate
            OR o2.EndDate BETWEEN o.BegDate AND o.EndDate
            OR o.BegDate BETWEEN o2.BegDate AND o2.EndDate
            OR o.EndDate BETWEEN o2.BegDate AND o2.EndDate
        )
    AND o2.TableId != o.TableId
)
ORDER BY o.SubType, o.Cause, o.BegDate

Demo link: http://rextester.com/KBFX30109

As @HABO suggestion, you also can use to check overlapping

    AND o2.BegDate <= o.EndDate 
    AND o.BegDate <= o2.EndDate

Upvotes: 4

Chirag Rupani
Chirag Rupani

Reputation: 1715

You need to check start overlap and end overlap. In case, startdate is always less than endDate, following query can be used. The CTE is not required if you have some primary key:

; WITH CTE AS
(
    SELECT *,
    ROW_NUMBER() OVER(
            PARTITION BY (SELECT NULL)
            ORDER BY (SELECT NULL)
    ) AS pk FROM #Overlap
)
SELECT a.*, b.pk, b.BegDate, b.endDate FROM CTE a
CROSS JOIN CTE b 
WHERE -- Simplify by adding PK
(a.pk <> b.pk)
AND (CASE WHEN (a.BegDate > b.BegDate) THEN a.BegDate ELSE b.BegDate END)
 <= (CASE WHEN (a.endDate < b.endDate) THEN a.endDate ELSE b.endDate END)

Upvotes: 4

Related Questions