ChrisM@Work (8/14/2012)

---

Here's an alternative using PARSENAME()

SELECT

bi.Building_Name,

COUNT(di.DeviceNum) NumberOfDevices

FROM #Building_Information bi

INNER JOIN #Device_Information di

ON PARSENAME(bi.Subnet,4) = PARSENAME(di.IP_Address,4)

AND PARSENAME(bi.Subnet,3) = PARSENAME(di.IP_Address,3)

AND PARSENAME(bi.Subnet,2) = PARSENAME(di.IP_Address,2)

GROUP BY Building_Name;