n        m1          m2          
-------- ----------- ----------- 
a        1           1
a        3           6
a        8           9
a        12          12
b        1           2
b        5           5

CREATE TABLE #T(Oct3 int, Oct4 int, Usage NVARCHAR(20))
GO
INSERT INTO #T (oct3, oct4, Usage)
SELECT 2,0, 'Network Address' UNION ALL
SELECT 2,1, 'Unallocated' UNION ALL
SELECT 2,2, 'Unallocated' UNION ALL
SELECT 2,3, 'Unallocated' UNION ALL
SELECT 2,4, 'Unallocated' UNION ALL
SELECT 2,5, 'Web Site' UNION ALL
SELECT 2,6, 'Web Site' UNION ALL
SELECT 2,7, 'Web Site' UNION ALL
SELECT 2,8, 'Web Site' UNION ALL
SELECT 2,9, 'Web Site' UNION ALL
SELECT 2,10, 'Unallocated' UNION ALL
SELECT 2,11, 'Unallocated' UNION ALL
SELECT 2,12, 'Unallocated' UNION ALL
SELECT 2,13, 'Server' UNION ALL
SELECT 2,14, 'Server' UNION ALL
SELECT 2,15, 'Broadcast Address' UNION ALL
SELECT 2,16, 'Another' UNION ALL
SELECT 3,1,'Another' UNION ALL
SELECT 3,2,'Another' UNION ALL
SELECT 3,6,'One More' UNION ALL
SELECT 3,10,'One More'
GO

select Usage,
  '192.168.' + convert(varchar, min(tmp) / 256) + '.' + 
     convert(varchar, min(tmp) % 256) as FromIP,
  '192.168.' + convert(varchar,max(tmp) / 256) + '.' + 
     convert(varchar, max(tmp) % 256) as ToIP, 
  count(*) as [# of Addresses]
from
(
  select a.*, 
     (select count(*) 
      from #t b 
      where 
         ((b.Oct3 < a.Oct3) or 
          (b.Oct3 = A.Oct3 and b.Oct4 <= a.oct4)) 
         and a.Usage<> B.Usage) as RunGroup
from 
   (select #t.*, oct3 * 256 + oct4 as tmp from #t) a
) a
group by 
  usage, runGroup

GO
DROP TABLE #t

quote:

---

Originally posted by jshepler

BegIP		EndIP		Usage
-------------------------------------------------
192.168.2.0	192.168.2.0	Network Address
192.168.2.1	192.168.2.4	Unallocated
192.168.2.5	192.168.2.10	Web Site
192.168.2.11	192.168.2.12	Unallocated
192.168.2.13	192.168.2.14	Server
192.168.2.15	192.168.2.15	Broadcast Address

---

                       this is result
 table h               of above query   
-------------        ------------------
n        m            n      m1     m2
-------- ----        ----   ----   ----
a        1            a      1      1
a        3            a      3      6
a        4            a      8      9
a        4            a      12     12
a        5            b      1      2
a        6            b      5      5
a        6           ------------------
a        6
a        8
a        9
a        12
b        1
b        1
b        2
b        2
b        5
------------

Usage          FromIP         ToIP          # of Addresses 
-------------- -------------- ------------- -------------- 
Unallocated    192.168.2.1    192.168.3.0   8

Usage          FromIP         ToIP
-------------- -------------- ------------- 
Unallocated    192.168.2.1    192.168.2.3
Unallocated    192.168.2.6    192.168.2.6
Unallocated    192.168.2.10   192.168.2.11
Unallocated    192.168.2.255  192.168.3.0

quote:

---

Originally posted by X002548

quote:

---

Originally posted by jshepler

BegIP		EndIP		Usage
-------------------------------------------------
192.168.2.0	192.168.2.0	Network Address
192.168.2.1	192.168.2.4	Unallocated
192.168.2.5	192.168.2.10	Web Site
192.168.2.11	192.168.2.12	Unallocated
192.168.2.13	192.168.2.14	Server
192.168.2.15	192.168.2.15	Broadcast Address

---

Shouldn't Web site be 5-9? Unallocated be 10-12?

If not, I'm missing something....



Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

---

quote:

---

Originally posted by jsmith8858

absolutely ... depends what he needs and what his data looks like.

- Jeff

---

CREATE VIEW _IPUsage
as
SELECT Network + '.' + CAST(IP AS VARCHAR) AS 'IP', ISNULL(IPUsage, 'Unallocated') AS 'Usage', SubnetID,
	CAST(RIGHT(Network, CHARINDEX('.', REVERSE(Network)) - 1) AS TinyINT) * 256 + IP AS 'Hash'
FROM IPs INNER JOIN Networks ON IPs.NetworkID = Networks.NetworkID
	LEFT JOIN IPUsages ON IPs.IPUsageID = IPUsages.IPUsageID

select	Beg.IP as BegIP, [End].IP as EndIP, Beg.Usage
from	(select min(hash) as MinHash, max(hash) as MaxHash, usage
	from	(select	Hash, Usage,
			(select count(*)
				from _IPUsage IU1
				where IU1.Usage <> IU2.Usage and
					IU1.Hash <= IU2.Hash and
			) as RunGroup
			from _IPUsage IU2
		) a
	group by Usage, RunGroup
) IU
INNER JOIN _IPUsage Beg ON IU.MinHash = Beg.Hash
INNER JOIN _IPUsage [End] ON IU.MaxHash = [End].Hash
order by IU.MinHash

quote:

---

However, I think I got the idea (I got the results I wanted) and I'm going to try it using numerical fields for the octets instead of a string. Then I can skip the view. Should be much faster.

---

select	cast(oct1 as varchar) + '.' + cast(oct2 as varchar) + '.' + cast(min(hash) / 256 as varchar) + '.' + cast(min(hash) % 256 as varchar) as 'BegIP',
	cast(oct1 as varchar) + '.' + cast(oct2 as varchar) + '.' + cast(max(hash) / 256 as varchar) + '.' + cast(max(hash) % 256 as varchar) as 'EndIP',
	Usage
from	(select Oct1, Oct2, (oct3 * 256) + IP as 'Hash', ISNULL(IPUsage, 'Unallocated') as Usage,
		(select	count(*)
			from IPs I1 INNER JOIN Networks N1 ON I1.NetworkID = N1.NetworkID
				LEFT JOIN IPUsages IPU1 ON I1.IPUsageID = IPU1.IPUsageID
			where	I1.IPUsageID <> I2.IPUsageID AND
				(N1.Oct3 * 256) + I1.IP <= (N2.Oct3 * 256) + I2.IP and
				subnetid = 204
		) as RunGroup
	from	IPs I2 INNER JOIN Networks N2 ON I2.NetworkID = N2.NetworkID
		LEFT JOIN IPUsages IPU2 ON I2.IPUsageID = IPU2.IPUsageID
	where	subnetid = 204
	) a
Group By RunGroup, usage, Oct1, Oct2
ORDER BY min(hash)