Find Conflicting Overlapping Dates from one table to another table

Question

I have a set of records in a table called #MPR. What I am trying to accomplish is find any records in that table that have overlapping dates in another table called #Lookup and have the matching ProviderID, Sub Type, CauseID and CircuitID. If so, return all records in the #MPR table and the corresponding overlapping record in the #Lookup table with the following columns LookupID, BegDate, EndDate and Amt.

-- Create Temporary Table
CREATE TABLE #MPR

(
MprID int IDENTITY(1,1),
ProviderID int,
RowNo int,
SubType varchar(50), 
CauseID int,
CircuitID int,
BegDate date,
EndDate date,
Amt decimal(11,2),
Remarks varchar(max)
)

--Insert records

INSERT INTO #MPR  VALUES(673,2,'Original',439,4852,'2016-12-01','2016-12-31',100,'Rec A')
INSERT INTO #MPR VALUES(673,15,'Original',439,4852,'2016-12-21','2016-12-31',200,'Rec B')
INSERT INTO #MPR VALUES(100,21,'Original',500,5000,'2016-12-01','2016-12-01',300,'Rec C')
INSERT INTO #MPR VALUES(673,24,'Original',439,4852,'2016-12-05','2017-01-31',400,'Rec D')

-- Create Temporary Table
CREATE TABLE #Lookup

(
LookupID int IDENTITY(1,1),
ProviderID int,
SubType varchar(50), 
CauseID int,
CircuitID int,
BegDate date,
EndDate date,
Amt decimal(11,2),
)

--Insert records
INSERT INTO #Lookup  Values(673,'Original',439,4852,'2016-10-01','2016-10-31',200.00)
INSERT INTO #Lookup Values(100,'Original',500,5000,'2016-10-01','2016-10-31',200.00)
INSERT INTO #Lookup Values(100,'Original',500,5000,'2016-10-01','2016-11-30',200.00)
INSERT INTO #Lookup Values(673,'Original',439,4852,'2016-11-01','2016-11-30',200.00)
INSERT INTO #Lookup Values(673,'Original',439,4852,'2016-12-01','2016-12-31',200.00)
INSERT INTO #Lookup Values(673,'Original',439,4852,'2017-01-01','2017-01-31',200.00)
INSERT INTO #Lookup Values(100,'Original',500,5000,'2016-12-01','2016-12-31',200.00)
INSERT INTO #Lookup Values(673,'Original',439,6565,'2017-01-01','2017-01-31',200.00)

--Drop table #mpr
--Drop table #Lookup

My Result:

MprID       ProviderID  RowNo       SubType  CauseID     CircuitID   BegDate    EndDate    Amt       LookupID    BegDate    EndDate    Amt
----------- ----------- ----------- -------- ----------- ----------- ---------- ---------- --------- ----------- ---------- ---------- --------
1           673         2           Original 439         4852        2016-12-01 2016-12-31 100.00    5           2016-12-01 2016-12-31 200.00
2           673         15          Original 439         4852        2016-12-21 2016-12-31 200.00    5           2016-12-01 2016-12-31 200.00
3           100         21          Original 500         5000        2016-12-01 2016-12-01 300.00    7           2016-12-01 2016-12-31 200.00
4           673         24          Original 439         4852        2016-12-05 2017-01-31 400.00    5           2016-12-01 2016-12-31 200.00
4           673         24          Original 439         4852        2016-12-05 2017-01-31 400.00    6           2017-01-01 2017-01-31 200.00

Please check out [this](https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap/325964#325964) so question. — Charles Bretana, Jul 18 '17 at 22:41
When you say "return all records in the #MPR table", this is a little confusing. Do you mean all columns? If you wanted to see more rows, how would those relate to the rows that overlapped with the `#Lookup` table? — ErikE, Jul 18 '17 at 23:15
@ErikE, you were able to figure out what I needed in your post below. That is exactly what I needed. — Charles Bernardes, Jul 19 '17 at 13:29
If my answer solved your issue please select it as the chosen solution by clicking the check mark next to it! — ErikE, Jul 19 '17 at 14:42

score 2 · Accepted Answer · answered Jul 18 '17 at 23:14

If I've read your specs correctly, this will do what you want. Notice that it uses only two conditions rather than four as in another answer so far.

SELECT
   m.*,
   l.LookupId,
   l.BegDate,
   l.EndDate,
   l.Amt
FROM
   #MPR m
   INNER JOIN #Lookup l
      ON m.ProviderID = l.ProviderID
      AND m.SubType = l.SubType
      AND m.CauseID = l.CauseID
      AND m.CircuitID = l.CircuitID
WHERE
   m.BegDate < l.EndDate
   AND l.BegDate < m.EndDate
;

score 1 · Answer 2 · answered Jul 18 '17 at 23:04

1

I think this will do what you want

select * 
from #mpr M 
inner join #Lookup L 
on M.ProviderID = L.ProviderID 
AND M.SubType = L.SubType 
AND M.CauseID = L.CauseID 
AND M.CircuitID = L.CircuitID
AND (M.BegDate BETWEEN L.BegDate AND L.EndDate OR L.BegDate BETWEEN M.BegDate AND M.EndDate)

answered Jul 18 '17 at 23:04

asmgx

5,268
9
47
92

This has 4 conditions (BETWEEN is an OR statement behind the scenes). See my answer for a way to do it in 2 conditions. – ErikE Jul 29 '17 at 19:03

Find Conflicting Overlapping Dates from one table to another table

2 Answers2