Desing of a table and Hirerarchy - IP's
-
wanttolearn1 (12/14/2016)
to be more clear, its a little bit more complicated as the print screen show :i can put under 10.20.12.0 (subnet 24) aagain same ip 10.20.12.0 (but with subnet 30 which mean smaller range of ip's)
Just to be even more clear, what ARE the actual requirements and what DOES the actual data look like? I noticed on your last post that you added a "/nn test n" string to the right-most octet. Please use the example I've been using in my code to simulate your data. Graphics don't do a bloody thing for our tests trying to help you.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
this is the solution i got and it work, the only problem that it runs for 1.42 minute, and the profiler dont give me any hint to
improve...
the point is to get result of table with ordered ip with there subnets
use tempdb
go
drop table Network
go
create table Network
(
First bigint,
Last bigint,
Size as 1 + Last - First persisted,
Name as concat(first,' - ', last) persisted
constraint pk_Network primary key (First,Last)
)
go
CREATE FUNCTION dbo.IPAddressToInteger (@IP AS varchar(15))
RETURNS bigint
AS
BEGIN
RETURN (CONVERT(bigint, PARSENAME(@IP,1)) +
CONVERT(bigint, PARSENAME(@IP,2)) * 256 +
CONVERT(bigint, PARSENAME(@IP,3)) * 65536 +
CONVERT(bigint, PARSENAME(@IP,4)) * 16777216)
END
go
insert into Network(First,Last)
values (dbo.IPAddressToInteger('10.0.0.0'),dbo.IPAddressToInteger('10.255.255.255')),
(dbo.IPAddressToInteger('10.0.0.0'),dbo.IPAddressToInteger('10.0.255.255')),
(dbo.IPAddressToInteger('10.1.0.0'),dbo.IPAddressToInteger('10.1.255.255')),
(dbo.IPAddressToInteger('10.1.2.0'),dbo.IPAddressToInteger('10.1.2.255')),
(dbo.IPAddressToInteger('10.1.4.0'),dbo.IPAddressToInteger('10.1.4.255'))
go
with NetworkHierarchy as
(
select top 1 with ties Name, First, Last, cast( null as bigint) ParentFirst, cast( null as bigint) ParentLast, 0 as level
from Network
order by size desc
union all
select n.Name, n.First,n.Last,nh.First,nh.Last,nh.level + 1
from NetworkHierarchy nh
join network n
on n.First >= nh.First
and n.Last <= nh.Last
and not (n.First = nh.First and n.Last = nh.Last)
), NetworkHierarchy2 as
(
select *, row_number() over (partition by name order by level desc) rn
from NetworkHierarchy
)
select Name, First, Last, ParentFirst, ParentLast, Level
from NetworkHierarchy2
where rn = 1
order by ParentFirst, parentlast
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply