CODEWITHSUNDEEP
sql-serversql-server-2016
B.Balamanigandan
B.Balamanigandan

Reputation: 4875

Find the available Range from a Table in SQL Server

I'm having a table, which contains Start and End columns both are INT. I need to find the very first available N Range comparing with the existing data.

Table Schema:

CREATE TABLE [dbo].[MSRange]
(
    [RangeId] [int] IDENTITY(1,1) NOT NULL,
    [RangeStart] [int] NOT NULL,
    [RangeEnd] [int] NOT NULL,
    CONSTRAINT [PK_MSRange] 
       PRIMARY KEY CLUSTERED ([RangeId] ASC)
) ON [PRIMARY]

Sample Seed Data:

INSERT INTO [dbo].[MSRange] ([RangeStart], [RangeEnd])
VALUES (1, 150), (1250, 1500), (3100, 7500), (10500, 15000);

Requirement:

I need to find the placeholder for 1000 slots, obviously from the seed data we can say 151 to 1150 is available. Similarly for 1500 slots means 1501 to 3000 is available.

Kindly assist me how to get the first available placeholder.

Upvotes: 4

Views: 92

Answers (1)

sepupic
sepupic

Reputation: 8687

declare @MSRange table
(
    [RangeId] [int] IDENTITY(1,1) NOT NULL primary key,
    [RangeStart] [int] NOT NULL,
    [RangeEnd] [int] NOT NULL
) 

INSERT INTO @MSRange ([RangeStart], [RangeEnd])
VALUES (1, 150), (1250, 1500), (3100, 7500), (10500, 15000);

declare @N int = 1000;

with cte as
(
select RangeId, 
       RangeEnd as result_range_start, 
       isnull(lead(RangeStart) over(order by RangeId), 2147483647) as result_range_end

from @MSRange
)

select top 1 result_range_start + 1, result_range_start + @N
from cte
where result_range_end - result_range_start > @N
order by RangeId;

This code is for versions starting with 2012. For @@version <= 2008 R2 the same idea but use row_number() to join then current with previous

Upvotes: 2

Related Questions