I am working on a project with an IP address database. When the
engineering types are assigning addresses to our hosting customers, I
would like my app to return the largest "contiguous" block that is
available to meet their request.

For Example if customer XYZ comes along and needs 10 IP Addresses, we
would rather give them 10 addresses that are concurrent/contiguous,
rather than random throughout the range that we have.

Here is a bit of sample data:
ADDRID OCTETA OCTETB OCTETC OCTETD FULLIP
6 192 168 100 1 192.168.100.1
7 192 168 100 2 192.168.100.2
8 192 168 100 3 192.168.100.3
9 192 168 100 4 192.168.100.4
10 192 168 100 5 192.168.100.5
11 192 168 100 10 192.168.100.10
13 192 168 100 11 192.168.100.11
12 192 168 100 12 192.168.100.12
14 192 168 100 13 192.168.100.13
15 192 168 100 14 192.168.100.14
16 192 168 100 15 192.168.100.15
17 192 168 100 16 192.168.100.16
18 192 168 100 17 192.168.100.17
19 192 168 100 18 192.168.100.18
20 192 168 100 19 192.168.100.19

If I call the StoredProc (I am writing this in T-SQL) and tell it I
would like 10 IP Addresses, the goal is to return ID=11 thru 20, not
6 thru 15.

I have Googled this one quite a bit and can't come up with an elegent
(doesn't involve recursive looping on cursors) solution.

Any thoughts are GREATLY appreciated.

Thanks,

Hi

It is always better to post DDL ( CREATE TABLE statements etc..) and example
data ( as insert statements) along with the code you are currently using.
This will remove any ambiguities that you may have.

CREATE TABLE IPRanges (
ADDRID tinyint not null,
OCTETA tinyint not null,
OCTETB tinyint not null,
OCTETC tinyint not null,
OCTETD tinyint not null,
FULLIP char(15))

INSERT INTO IPRanges ( ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP )
VALUES(
6, 192, 168, 100, 1, '192.168.100.1')
INSERT INTO IPRanges ( ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP )
VALUES(
7, 192, 168, 100, 2, '192.168.100.2')
INSERT INTO IPRanges ( ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP )
VALUES(
8, 192, 168, 100, 3, '192.168.100.3')
INSERT INTO IPRanges ( ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP )
VALUES(
9, 192, 168, 100, 4, '192.168.100.4')
INSERT INTO IPRanges ( ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP )
VALUES(
10, 192, 168, 100, 5, '192.168.100.5')
INSERT INTO IPRanges ( ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP )
VALUES(
11, 192, 168, 100, 10, '192.168.100.10')
INSERT INTO IPRanges ( ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP )
VALUES(
13, 192, 168, 100, 11, '192.168.100.11')
INSERT INTO IPRanges ( ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP )
VALUES(
12, 192, 168, 100, 12, '192.168.100.12')
INSERT INTO IPRanges ( ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP )
VALUES(
14, 192, 168, 100, 13, '192.168.100.13')
INSERT INTO IPRanges ( ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP )
VALUES(
15, 192, 168, 100, 14, '192.168.100.14')
INSERT INTO IPRanges ( ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP )
VALUES(
16, 192, 168, 100, 15, '192.168.100.15')
INSERT INTO IPRanges ( ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP )
VALUES(
17, 192, 168, 100, 16, '192.168.100.16')
INSERT INTO IPRanges ( ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP )
VALUES(
18, 192, 168, 100, 17, '192.168.100.17')
INSERT INTO IPRanges ( ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP )
VALUES(
19, 192, 168, 100, 18, '192.168.100.18')
INSERT INTO IPRanges ( ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP )
VALUES(
20, 192, 168, 100, 19, '192.168.100.19')

The following seems to work, but just allocating a range that fits may not
be what is required as it could leave you with alot of small ranges that you
can not fill. This has also not been checked with different subnets.

CREATE PROCEDURE GetNextRange ( @RequiredRange INT ) AS
SELECT TOP 1 ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP, NextOctetD,
NextOctetD - OCTETD AS AvailableRange
FROM (
SELECT ADDRID, OCTETA, OCTETB, OCTETC, OCTETD, FULLIP,
ISNULL((SELECT TOP 1 OCTETD FROM IPRanges i2
WHERE i2.OCTETD > i1.OCTETD
and i2.OCTETA = i1.OCTETA
and i2.OCTETB = i1.OCTETB
and i2.OCTETC = i1.OCTETC
ORDER BY OCTETA, OCTETB, OCTETC, OCTETD ) ,255) as NextOctetD
FROM IPRanges i1
) A
WHERE NextOctetD - OCTETD >= @RequiredRange
ORDER BY NextOctetD - OCTETD, ADDRID

John

[quoted text, click to view]

[quoted text, click to view]

Hi Robin,

The most straightforward (not necessarily the fastest) solution would be
something like this:

SELECT 'From', OctetA, OctetB, OctetC, OctetD + 1,
'Up to', OctetA, OctetB, OctetC, OctetD + @NumRequired
FROM IPAddressesGiven
WHERE FullIP =
(SELECT MIN(FullIP)
FROM IPAddressesGiven IP1
WHERE NOT EXISTS
(SELECT *
FROM IPAddressesGiven IP2
WHERE IP2.OctetA = IP1.OctetA
AND IP2.OctetB = IP1.OctetB
AND IP2.OctetC = IP1.OctetC
AND IP2.OctetD > IP1.OctetD
AND IP2.OctetD <= IP1.OctetD + @NumRequired)
AND IP1.OctedD < 254 - @NumRequired)
(untested)

This won't give out addresses in a completely new range (with a new value
for OctetA, OctetB or OctetC). Since SQL Server doesn't know what IP
ranges your company may use, this is impossible to do without additional
information in a table. You might want to consider changing your table
design: populate the table with EVERY IP address your company may use and
add one column that indicates for which customer an IP address is used; a
NULL in that column indicates the IP address is available.

BTW, I hope that FullIP is a computed column.....

Best, Hugo
--