How to merge ranges from different tables

sql, sql-server, t-sql

Solution

First I declare data that looks like the data you posted. Please correct me if any assumptions I have made are wrong. Better would be to post your own declaration in the question so we are all working with the same data.

DECLARE @T1 TABLE (
  [From] INT,
  [To] INT,
  [Value] CHAR(3)
);

INSERT INTO @T1 (
  [From],
  [To],
  [Value]
)
VALUES
  (10, 20, 'XXX'),
  (20, 30, 'YYY'),
  (30, 40, 'ZZZ');

DECLARE @T2 TABLE (
  [From] INT,
  [To] INT,
  [Value] CHAR(3)
);

INSERT INTO @T2 (
  [From],
  [To],
  [Value]
)
VALUES
  (10, 15, 'AAA'),
  (15, 19, 'BBB'),
  (19, 39, 'CCC'),
  (39, 40, 'DDD');

Here is my select query to generate your expected result:

SELECT
  CASE
    WHEN [@T1].[From] > [@T2].[From]
    THEN [@T1].[From]
    ELSE [@T2].[From]
  END AS [From],
  CASE
    WHEN [@T1].[To] < [@T2].[To]
    THEN [@T1].[To]
    ELSE [@T2].[To]
  END AS [To],
  [@T1].[Value],
  [@T2].[Value]
FROM @T1
INNER JOIN @T2 ON
  (
    [@T1].[From] <= [@T2].[From] AND
    [@T1].[To] > [@T2].[From]
  ) OR
  (
    [@T2].[From] <= [@T1].[From] AND
    [@T2].[To] > [@T1].[From]
  );

Problem

Original source