How to define ranges of overlap numbers?
I have two tables TABLE1 (List of records) and TABLE2 (My number ranges to check)
How to define overlap / row (row) ranges in table 2?
TARGET
RECORDS TABLE
+-----------------+-------------------+
+ tbl1_NumberFrom + tbl1_NumberTo +
+-----------------+-------------------+
+ 0 + 10 +
+ 15 + 20 +
+ 50 + 100 +
+-----------------+-------------------+
MY RANGES TABLE
+-----------------+-------------------+
+ My_NumberFrom + My_NumberTo +
+-----------------+-------------------+
+ 5 + 20 +
+ 30 + 40 +
+ 12 + 15 +
+-----------------+-------------------+
REQUIRED RESULT AS FOLLOW:
+-----------------+-------------------+--------------+
+ My_NumberFrom + My_NumberTo + IsOverlaping +
+-----------------+-------------------+--------------+
+ 5 + 20 + Yes +
+ 30 + 40 + No +
+ 12 + 15 + Yes +
+-----------------+-------------------+--------------+
FURTHER MORE,
SQL for the above tables
IF OBJECT_ID('tempdb.dbo.#dbtable', 'U') IS NOT NULL DROP TABLE #dbtable;
SELECT * INTO #dbtable FROM
(SELECT 0 AS tbl1_NumberFrom, 10 AS tbl1_NumberTo
UNION ALL
SELECT 15 AS tbl1_NumberFrom, 20 AS tbl1_NumberTo
UNION ALL
SELECT 50 AS tbl1_NumberFrom, 100 AS tbl1_NumberTo)
AS dbTable
IF OBJECT_ID('tempdb.dbo.#MyRanges', 'U') IS NOT NULL DROP TABLE #MyRanges;
SELECT * INTO #MyRanges FROM
(SELECT 5 AS MyTbl_NumberFrom, 20 AS MyTbl_NumberTo
UNION ALL
SELECT 30 AS MyTbl_NumberFrom, 40 AS MyTbl_NumberTo
UNION ALL
SELECT 12 AS MyTbl_NumberFrom, 15 AS MyTbl_NumberTo)
AS MyList
SELECT * FROM #dbtable t
SELECT * FROM #MyRanges m
Please suggest,
Many thanks!
+3
source to share
1 answer
Use outer join to see if there is overlap. Three conditions will cover all types of floors.
Select distinct m.MyTbl_NumberFrom,m.MyTbl_NumberTo,
case when t.tbl1_NumberFrom is null then 'No' else 'yes' end isOverlapping
from #MyRanges m
left join #dbtable t on m.MyTbl_NumberFrom between t.tbl1_NumberFrom and t.tbl1_NumberTo
or m.MyTbl_Numberto between t.tbl1_NumberFrom and t.tbl1_NumberTo
or t.tbl1_NumberFrom between m.MyTbl_NumberFrom and m.MyTbl_Numberto
+1
source to share