CODEWITHSUNDEEP
sqlsql-servergaps-and-islands
Lajith
Lajith

Reputation: 1867

Splits records based on range

I have 2 tables Item and Item Range. In Item table all items are exists in range i.e. (From and To) numbers. I have to exclude those number from ItemRange table i.e (50 to 60 and 70 to 80). 

Declare @Item table
(
    Id int primary key, 
    ItemId int,
    [FROM] int,
    [To] int
)

Declare @ItemRange table
(
    Id int primary key, 
    ItemId int,
    [FROM] int,
    [To] int
)

INSERT INTO @Item 
VALUES  
(1,1,1,100),
(2,1,101,500),
(3,1,600,700)

INSERT INTO @ItemRange 
VALUES  
(1,1,50,60),
(2,1,70,80)

Expected Result:

The result in first row 1 to 49 beacuse 50-60 are in the table itemrange...then in 2nd row 61 to 69 because 70-80 in the table itemrange.. then 81 to 500 then 600-800 same as table itemrange because no range row exist in the table itemrange..if any row find in the table itemrange in the result it should split to 2 records...pls anyone help me

Upvotes: 3

Views: 148

Answers (1)

D-Shih
D-Shih

Reputation: 46229

This is a islands and gaps problem.

You can try to use two cte recursive then do except.

Final use Row_number window function to get the gap number, then group by it.

MS SQL Server 2017 Schema Setup:

CREATE  table Item
(
    Id int primary key, 
    ItemId int,
    [FROM] int,
    [To] int
)

CREATE table ItemRange
(
    Id int primary key, 
    ItemId int,
    [FROM] int,
    [To] int
)

INSERT INTO Item 
VALUES  
(1,1,1,100),
(2,1,101,500),
(3,1,600,700)

INSERT INTO ItemRange 
VALUES  
(1,1,50,60),
(2,1,70,80)

Query 1:

;WITH CTE AS (
  SELECT ItemId,[FROM],[TO]
  FROM Item
  UNION ALL
  SELECT ItemId,[FROM]+ 1,[TO]
  FROM CTE
  WHERE [FROM]+ 1 <= [TO]
), CTE2 AS(
  SELECT ItemId,[FROM],[TO]
  FROM ItemRange
  UNION ALL
  SELECT  ItemId,[FROM]+ 1,[TO]
  FROM CTE2
  WHERE [FROM]+ 1 <= [TO]
),CTE3 AS(
  SELECT ItemId,[FROM]
  FROM CTE
  except
  SELECT ItemId,[FROM]
  FROM CTE2
)
SELECT ItemId,
       MIN([FROM]) 'FROM',
       MAX([FROM]) 'TO'
FROM (
  SELECT ItemId,[FROM],[FROM] - ROW_NUMBER() OVER(ORDER BY [FROM]) grp
  FROM CTE3
) t1
GROUP BY grp,ItemId
option (maxrecursion 0)

Results:

| ItemId | FROM |  TO |
|--------|------|-----|
|      1 |    1 |  49 |
|      1 |   61 |  69 |
|      1 |   81 | 500 |
|      1 |  600 | 700 |

Upvotes: 2

Related Questions