Consulta SQL aninhada / unida para extrair dados do telefone do banco de dados do Cisco UCM

Question

Preciso consultar números de telefones por site para telefones analógicos e IP separadamente. Eu posso fazer isso com essas duas perguntas.

Telefones IP:

select count(d.name) as IP_Phones, dp.name as DevicePool 
from Device as d 
inner join DevicePool as dp on d.fkDevicePool=dp.pkid 
inner join typemodel as tm on tm.enum=d.tkmodel 
where (tm.name != 'Analog Phone' and tm.name != 'Conference Bridge'
    and tm.name != 'CTI Route Point' and tm.name != 'CTI Port'
    and tm.name != 'MGCP Station' and tm.name != 'Route List'
    and tm.name != 'H.323 Gateway' 
    and tm.name != 'Music On Hold' 
    and tm.name != 'Media Termination Point' 
    and tm.name != 'Tone Announcement Player'
    and tm.name != 'Cisco IOS Conference Bridge (HDV2)'
    and tm.name != 'Cisco IOS Software Media Termination Point (HDV2)' 
    and tm.name != 'Cisco IOS Media Termination Point (HDV2)' 
    and tm.name != 'SIP Trunk' and dp.name like '%PH%') 
group by dp.name
order by dp.name

que resulta em

ip_phones devicepool
========= ================
815       Site1-DP
43        Site2-DP
32        Site3-DP
890       Site4-DP

Telefones analógicos:

select count(d.name) as Analog_Phones, dp.name as DevicePool
from Device as d 
inner join DevicePool as dp on d.fkDevicePool=dp.pkid 
inner join typemodel as tm on tm.enum=d.tkmodel 
where (tm.name = 'Analog Phone' and dp.name like '%PH%') 
group by dp.name 
order by dp.name

que resulta em

analog_phones devicepool ============= ============== 12 Site1-DP 14 Site2-DP 1 Site3-DP 4 Site4-DP

O que estou procurando é uma única consulta que resulte em algo assim:

ip_phones analog_phones devicepool
========= ============= ==========
815       12            Site1-DP
43        14            Site2-DP
32        1             Site3-DP
890       4             Site4-DP    

    

Answer 1

Isso deve ser feito. A ideia é pegar as duas consultas, uni-las e agrupá-las no pool de dispositivos para que você tenha uma linha por pool.

SELECT sum(analog_phones) as analog_phones,
       sum(ip_phones) as ip_phones,
       devicepool
FROM
  (SELECT 0 AS analog_phones,
                  count(d.name) AS IP_Phones,
                  dp.name AS DevicePool
   FROM Device AS d
   INNER JOIN DevicePool AS dp ON d.fkDevicePool=dp.pkid
   INNER JOIN typemodel AS tm ON tm.enum=d.tkmodel
   WHERE (tm.name != 'Analog Phone'
          AND tm.name != 'Conference Bridge'
          AND tm.name != 'CTI Route Point'
          AND tm.name != 'CTI Port'
          AND tm.name != 'MGCP Station'
          AND tm.name != 'Route List'
          AND tm.name != 'H.323 Gateway'
          AND tm.name != 'Music On Hold'
          AND tm.name != 'Media Termination Point'
          AND tm.name != 'Tone Announcement Player'
          AND tm.name != 'Cisco IOS Conference Bridge (HDV2)'
          AND tm.name != 'Cisco IOS Software Media Termination Point (HDV2)'
          AND tm.name != 'Cisco IOS Media Termination Point (HDV2)'
          AND tm.name != 'SIP Trunk'
          AND dp.name LIKE '%PH%')
   GROUP BY dp.name
   UNION ALL SELECT count(d.name) AS Analog_Phones,
                    0 AS ip_phones,
                            dp.name AS DevicePool
   FROM Device AS d
   INNER JOIN DevicePool AS dp ON d.fkDevicePool=dp.pkid
   INNER JOIN typemodel AS tm ON tm.enum=d.tkmodel
   WHERE (tm.name = 'Analog Phone'
          AND dp.name LIKE '%PH%')
   GROUP BY dp.name) a
GROUP BY devicepool
ORDER BY devicepool