CODEWITHSUNDEEP
sqlt-sqldatejoin
Charles Bernardes
Charles Bernardes

Reputation: 313

Find Conflicting Overlapping Dates from one table to another table

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

Upvotes: 0

Views: 48

Answers (2)

ErikE
ErikE

Reputation: 50272

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
;

Upvotes: 2

asmgx
asmgx

Reputation: 8054

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)

Upvotes: 1

Related Questions