SQL Server Merge two tables common and uncommon columns based on intervals of data SQL

ql3eal8s 于 2023-04-04 发布在其他

关注(0)|答案(1)|浏览(101)

I would love some help to solve my problem below please!

I have two SQL Server tables which I would like to merge into one result table. Table 1 has larger intervals (DistanceFrom - DistanceTo) than Table 2 so I am dealing with overlapping intervals. Sometimes it is perfect match for DistanceFrom and DistanceTo but other times not.

I would like to display the Table1.code associated to each Sample in the result table, knowing the DistanceFrom and DistanceTo can vary.

Table 1:

enter image description here

Table 2:

enter image description here

Desired result table:

enter image description here

Any help will be greatly appreciated!

Thanks

I managed to merge the 2 tables but always end up with some NULL in my Table1.Code result table as I struggle to split the intervals from Table 1 when required. The only time I don't have NULL is when the DIstanceFrom and Distance2 from table 1 are matching the ones from Table2.

Or I have managed to merge but I am missing some 'Sample' (missing the intervals not matching DistanceFrom and Distance To basically)

----To Ravi Hi Ravi and thanks for your quick answer. I think I may not have been clear enough and below is a better example with the results expected: So far I have managed to get most of my intervals using a different query but I have intervals still missing. The sample should be duplicated in some occasion like for U2 but the matching distancefrom and distanceTo should be different when there is a sample reapeat due to an overlapping code if it makes sense?

CREATE TABLE table1 ( Code VARCHAR(10) NULL ,DistanceFrom INT NULL ,DistanceTo INT NULL )

CREATE TABLE table2 ( Sample VARCHAR(10) NULL ,DistanceFrom INT NULL ,DistanceTo INT NULL ) INSERT INTO table1 VALUES ('A',68.04 ,74.58), ('B',74.58 ,74.85), ('C', 74.85, 75.07),('D', 75.07, 81.07)

INSERT INTO table2 VALUES ('U1',74 ,74.58), ('U2',74.58 ,75.07), ('U3', 75.07, 76), ('U4',76 ,77)

Below would be the results I would like to get:

Sample DistanceFrom DistanceTo Table1.Code U1 74 74.58 A U2 74.58 74.85 B U2 74.85 75.07 C U3 75.07 76 D U4 76 77 NULL

sql-server

来源：https://stackoverflow.com/questions/75910444/merge-two-tables-common-and-uncommon-columns-based-on-intervals-of-data-sql

1条答案

按热度按时间

mrfwxfqh1#

Here is the query:

DDL

CREATE TABLE table1 (
  Code VARCHAR(10) NULL
  ,DistanceFrom INT NULL
  ,DistanceTo INT NULL
)

CREATE TABLE table2 (
  Sample VARCHAR(10) NULL
  ,SDistanceFrom INT NULL
  ,SDistanceTo INT NULL
)

INSERT INTO table1 
VALUES ('C1',3 ,9), ('C2',9 ,10), ('C3', 10, 13)

INSERT INTO table2 
VALUES ('S1',3 ,4), ('S2',4 ,6), ('S3', 6, 9), ('S4',9 ,11), ('S5', 11, 13)

Select Query

SELECT t2.Sample
  ,IIF(t1.DistanceFrom >= t2.SDistanceFrom, t1.DistanceFrom, t2.SDistanceFrom) AS RDistanceFrom
  ,IIF(t1.DistanceTo <= t2.SDistanceTo, t1.DistanceTo, t2.SDistanceTo) AS RDistanceTo
  ,t1.Code
FROM table1 AS t1
JOIN table2 AS t2
  ON t2.SDistanceFrom BETWEEN t1.DistanceFrom AND t1.DistanceTo - 1
    OR t2.SDistanceTo BETWEEN t1.DistanceFrom + 1 AND t1.DistanceTo
ORDER BY t2.Sample, t1.Code

Explanation

JOIN: We are joining table1 and table2 in such way that if there is any overlap between DistanceFrom - DistanceTo and SDistanceFrom - SDistanceTo

SELECT:
RDistanceFrom : Maximum of DistanceFrom and SDistanceFrom
RDistanceTo : Minimum of DistanceTo and SDistanceTo

Please check SQL Fiddle and tweak your query incase it needs any change.

Regards, Ravi

赞(0）回复(0）举报 2023-04-04

我来回答

SQL Server Merge two tables common and uncommon columns based on intervals of data SQL

1条答案

DDL

Select Query

Explanation

相关问题

热门标签

最新问答