Tareas #138
open
Ausencia de VNOs en reporte comercial
100%
Description
Esteban me reporta que no aparecen registros en el reporte comercial de la tabla to_comercial.reporte_comercial_diario de las VNO: "Munip Escobar - 2800", "Munip S Martin - 2805" y "Munip S Fernando - 2806"
Files
Updated by Demo MiGestion365 Admin 3 days ago
- Status changed from Nueva to En curso
Analizando el caso detectamos que en la query del script, estas VNO se encuentran mencionadas en el "case" pero no en el "where" de la query:
with t_reporte as (
select
distinct (bi.access_id) as ACCESO,
case
when cast(bi.operatorid as varchar)='962' then 'SION TLS - 962'
when cast(bi.operatorid as varchar)='963' then 'SION Residencial - 963'
when cast(bi.operatorid as varchar)='1001' then 'TASA - 1001'
when cast(bi.operatorid as varchar)='2800' then 'Munip Escobar - 2800'
when cast(bi.operatorid as varchar)='2805' then 'Munip S Martin - 2805'
when cast(bi.operatorid as varchar)='2806' then 'Munip S Fernando - 2806'
when cast(bi.operatorid as varchar)='3001' then 'DTV - 3001'
when cast(bi.operatorid as varchar)='3950' then 'IPLAN - 3950'
when cast(bi.operatorid as varchar)='4000' then 'Metrotel - 4000'
else
cast(bi.operatorid as varchar)
end
as VNO,
case
when (s.serial_number not like 'ALCL0%' and s.serial_number is not null) then 'IN SERVICE'
else
'RESERVED'
end
as ESTADO,
bi.provided_date as FECHA_PROVISION,
csmb.partido_despliegue as PARTIDO_GEOGRAFICO_ONT,
csmb.cabecera as CABECERA,
bi.cable_puerta_olt as ALIMENTADOR,
bi.object_name as OBJECT_NAME
from
aux.bajada_inventario bi
join altiplano.serial s on
(split_part(s.object_name,'-',1)||':1-1-'||split_part(s.object_name,'-',2)||'-'||split_part(s.object_name,'-',3)||'-'||replace(split_part(s.object_name,'-',4),'_GPON','')) = bi.object_name
join cm.ci_sfat_mfat_bfat csmb
on
bi.cto = csmb.nfctag
where csmb.topologia ='G30'
and (
(bi.operatorid='1001' and s.vno='1001')
or (bi.operatorid='4000' and s.vno='4000')
or (bi.operatorid='3950' and s.vno='3950')
or (bi.operatorid='962' and s.vno='962')
or (bi.operatorid='1000' and s.vno='1000')
or (bi.operatorid='3001' and s.vno='3001')
)
and s.serial_number !=''
),
t_bws as (
select s.access_id , string_agg(concat('', pb."shaper-name"), '|') as BW from altiplano.perfil_bajada pb
join altiplano.serial s on s.object_name = pb."object-name"
---where (not pb."shaper-name" like '%GESTION%') and (not pb."shaper-name" like '%1kb%')
group by s.access_id
)
select * from t_reporte left join t_bws on t_bws.access_id=t_reporte.acceso
Esto hizo que analizara la tabla altiplano.serial, en la que efectivamente esos valores no existen.
Updated by Demo MiGestion365 Admin 3 days ago
- File clipboard-202609251240-9zrnn.png clipboard-202609251240-9zrnn.png added
- File clipboard-202609251241-h7iit.png clipboard-202609251241-h7iit.png added
- Due date set to 09/25/2026
- % Done changed from 0 to 100
- Estimated time set to 8:00 h
Luego de analizar la query que me compartió Esteban la reemplacé por la del script y efectivamente aparecen los valores deseados en la tabla reporte_comercial
with t_reporte as (
select
distinct (bi.access_id) as ACCESO,
case
when cast(bi.operatorid as varchar)='962' then 'SION TLS - 962'
when cast(bi.operatorid as varchar)='963' then 'SION Residencial - 963'
when cast(bi.operatorid as varchar)='1001' then 'TASA - 1001'
when cast(bi.operatorid as varchar)='2800' then 'Munip Escobar - 2800'
when cast(bi.operatorid as varchar)='2805' then 'Munip S Martin - 2805'
when cast(bi.operatorid as varchar)='2806' then 'Munip S Fernando - 2806'
when cast(bi.operatorid as varchar)='3001' then 'DTV - 3001'
when cast(bi.operatorid as varchar)='3950' then 'IPLAN - 3950'
when cast(bi.operatorid as varchar)='4000' then 'Metrotel - 4000'
else
cast(bi.operatorid as varchar)
end as VNO,
case
when (s.serial_number not like 'ALCL0%' and s.serial_number is not null) then 'IN SERVICE'
else
'RESERVED'
end as ESTADO,
bi.provided_date as FECHA_PROVISION,
csmb.partido_despliegue as PARTIDO_GEOGRAFICO_ONT,
csmb.cabecera as CABECERA,
bi.cable_puerta_olt as ALIMENTADOR,
bi.object_name as OBJECT_NAME
from
aux.bajada_inventario bi
join altiplano.serial s on
(split_part(s.object_name,'-',1)||':1-1-'||split_part(s.object_name,'-',2)||'-'||split_part(s.object_name,'-',3)||'-'||replace(split_part(s.object_name,'-',4),'_GPON','')) = bi.object_name
join cm.ci_sfat_mfat_bfat csmb
on
bi.cto = csmb.nfctag
where csmb.topologia ='G30'
and (
(bi.operatorid='1001' and s.vno='1001')
or (bi.operatorid='4000' and s.vno='4000')
or (bi.operatorid='3950' and s.vno='3950')
or (bi.operatorid='962' and s.vno='962')
or (bi.operatorid='1000' and s.vno='1000')
or (bi.operatorid='2800' and s.vno='1000')
or (bi.operatorid='2805' and s.vno='1000')
or (bi.operatorid='2806' and s.vno='1000')
or (bi.operatorid='963' and s.vno='963')
or (bi.operatorid='3001' and s.vno='3001')
)
and s.serial_number !=''
),
t_bws as (
select
s.access_id , string_agg(concat('', pb."shaper-name"), '|') as BW
from
altiplano.perfil_bajada pb
join altiplano.serial s on
s.object_name = pb."object-name"
---where (not pb."shaper-name" like '%GESTION%') and (not pb."shaper-name" like '%1kb%')
group by s.access_id
)
select * from t_reporte left join t_bws on t_bws.access_id=t_reporte.acceso
