SQLTeam.com | Weblogs | Forums

Counting hierarchically or recursively


#1

I work with a wireless internet company. We have access points that feed customers directly, and we have access points that feed other access points that feed customers. We also have access points that feed both other access points and customers directly. My goal is tell how many customers depend on a particular access point. I have a basic model of this working with joins, but I cannot figure out how to group them correctly to give me the real count. Maybe I need to use a different method completely, with loops or something else like this page http://articles.opwernby.com/RQuery+Recursive+Queries+in+SQL+Server/p1.html I've also included some detail of the output to try and describe what I'm trying to accomplish.

select nr.radio_name, sub.num, nr1.radio_name, sub1.num, nr2.radio_name, sub2.num
from network_radios nr
left join (select access_point, count(access_point) as num
	from customer c
	join networkinfo_tab nit on c.id=nit.d_custid
	where active='y' group by access_point) as sub on sub.access_point=nr.radio_name
join network_sites ns on ns.uplink_device_name=nr.radio_name
join network_radios nr1 on nr1.location_code=ns.code
left join (select access_point, count(access_point) as num
	from customer c
	join networkinfo_tab nit on c.id=nit.d_custid
	where active='y' group by access_point) as sub1 on sub1.access_point=nr1.radio_name
join network_sites ns1 on ns1.uplink_device_name=nr1.radio_name
join network_radios nr2 on nr2.location_code=ns1.code
left join (select access_point, count(access_point) as num
	from customer c
	join networkinfo_tab nit on c.id=nit.d_custid
	where active='y' group by access_point) as sub2 on sub2.access_point=nr2.radio_name
where nr.radio_name='01RLA65'
order by nr.radio_name

here's the sample output

radio_name	num	radio_name	num	 radio_name	num
01RLA65  	NULL 	65rla75 	2	 75AP5   	3
01RLA65 	NULL 	65rla75 	2	 75AP2.4 	6
01RLA65 	NULL 	65rla75 	2	 75RLC65	NULL

This is showing that there is an access point called 01RLA65 which feeds another access point 65rla75 which feeds 2 individual customers directly and also feeds another site which has three pieces of equipment (two access points 75AP5 and 75AP2.4 and one client radio which is not an access point). The result I am looking for would be that 01RLA65 and 65rla75 both have (2 + 3 + 6 = 11) dependent customers, while 75AP5 only has 3 dependent customers.
Thanks for any direction you can point me!


#2

I have another approach that may be easier. Here's the query to count the number of customers that depend on just one device--which is declared via the variable at the beginning. Now, I need to construct a loop that will cycle through a list of input variables (a list of access points that reside in another table) and run this query on each input variable. Can I get the results to show the input variable for each iteration, as well as the result of the query?


declare @radio varchar(20)
set @radio='01RLA65'

select count(c.id)
from customer c
join networkinfo_tab nit on nit.d_custid=c.id
join network_radios nr1 on nr1.radio_name=nit.access_point
join network_sites ns1 on ns1.code=nr1.location_code
left join network_radios nr2 on nr2.radio_name=ns1.uplink_device_name
left join network_sites ns2 on ns2.code=nr2.location_code
left join network_radios nr3 on nr3.radio_name=ns2.uplink_device_name
left join network_sites ns3 on ns3.code=nr3.location_code
left join network_radios nr4 on nr4.radio_name=ns3.uplink_device_name
left join network_sites ns4 on ns4.code=nr4.location_code
left join network_radios nr5 on nr5.radio_name=ns4.uplink_device_name
left join network_sites ns5 on ns4.code=nr5.location_code
left join network_radios nr6 on nr6.radio_name=ns5.uplink_device_name
left join network_sites ns6 on ns5.code=nr6.location_code
left join network_radios nr7 on nr6.radio_name=ns6.uplink_device_name
left join network_sites ns7 on ns5.code=nr6.location_code
left join network_radios nr8 on nr7.radio_name=ns6.uplink_device_name
where
ns1.uplink_device_name=@radio or
ns2.uplink_device_name=@radio or
ns3.uplink_device_name=@radio or
ns4.uplink_device_name=@radio or
ns5.uplink_device_name=@radio or
ns6.uplink_device_name=@radio or
ns7.uplink_device_name=@radio


#3

[http://stackoverflow.com/questions/15286255/loop-through-a-recordset-and-use-the-result-to-do-another-sql-select-and-return][1]
I was able to construct this loop with the help of a colleague and this website. Works like a champ. Hope it helps someone down the road.

    --Container to Insert Id which are to be iterated
Declare @temp1 Table
(
  tempRadioName varchar(30)
)
--Container to Insert records in the inner select for final output
Declare @FinalTable  Table
(
  ap varchar(30),
  num int
)

Insert into @temp1 
Select Distinct radio_name From network_radios where radio_name like 'can%'

-- Keep track of @temp1 record processing
Declare @radio varchar(30)
While((Select Count(*) From @temp1)>0)
Begin
   Set @radio=(Select Top 1 tempRadioName From @temp1)

   Insert Into @FinalTable 
		select @radio as ap, count(c.id) as num
		from customer c
		join networkinfo_tab nit on nit.d_custid=c.id
		join network_radios nr1 on nr1.radio_name=nit.access_point
		join network_sites ns1 on ns1.code=nr1.location_code
		left join network_radios nr2 on nr2.radio_name=ns1.uplink_device_name
		left join network_sites ns2 on ns2.code=nr2.location_code
		left join network_radios nr3 on nr3.radio_name=ns2.uplink_device_name
		left join network_sites ns3 on ns3.code=nr3.location_code
		left join network_radios nr4 on nr4.radio_name=ns3.uplink_device_name
		left join network_sites ns4 on ns4.code=nr4.location_code
		left join network_radios nr5 on nr5.radio_name=ns4.uplink_device_name
		left join network_sites ns5 on ns4.code=nr5.location_code
		left join network_radios nr6 on nr6.radio_name=ns5.uplink_device_name
		left join network_sites ns6 on ns5.code=nr6.location_code
		left join network_radios nr7 on nr6.radio_name=ns6.uplink_device_name
		left join network_sites ns7 on ns5.code=nr6.location_code
		left join network_radios nr8 on nr7.radio_name=ns6.uplink_device_name
		where 
		nit.access_point=@radio or
		ns1.uplink_device_name=@radio or 
		ns2.uplink_device_name=@radio or 
		ns3.uplink_device_name=@radio or 
		ns4.uplink_device_name=@radio or
		ns5.uplink_device_name=@radio or
		ns6.uplink_device_name=@radio or
		ns7.uplink_device_name=@radio

   Delete @temp1 Where tempRadioName=@radio
End

Select * From @FinalTable