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!
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
[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