-- FUENTES
SELECT * FROM mad.ay_crc_portados;
SELECT * FROM mad.ay_crc_quejas;

-- Tratamientos de las Bases
CREATE MULTISET TABLE MAD.AY_CRC_QUEJAS_DETALLE AS (
SELECT 
ANNO,       
TRIMESTRE,
CASE
WHEN TRIMESTRE='2' AND MES_DEL_TRIMESTRE = '1' THEN '4'
WHEN TRIMESTRE='2' AND MES_DEL_TRIMESTRE = '2' THEN '5'
WHEN TRIMESTRE='2' AND MES_DEL_TRIMESTRE = '3' THEN '6'
WHEN TRIMESTRE='3' AND MES_DEL_TRIMESTRE = '1' THEN '7'
WHEN TRIMESTRE='3' AND MES_DEL_TRIMESTRE = '2' THEN '8'
WHEN TRIMESTRE='3' AND MES_DEL_TRIMESTRE = '3' THEN '9'
WHEN TRIMESTRE='4' AND MES_DEL_TRIMESTRE = '1' THEN '10'
WHEN TRIMESTRE='4' AND MES_DEL_TRIMESTRE = '2' THEN '11'
WHEN TRIMESTRE='4' AND MES_DEL_TRIMESTRE = '3' THEN '12'
ELSE MES_DEL_TRIMESTRE END AS MES,
EMPRESA,
SERVICIO,
EMPAQUETADO,
TIPOLOGIA,
MEDIO_ATENCION,
NUMERO_QUEJAS
FROM mad.ay_crc_quejas
) WITH DATA PRIMARY INDEX (ANNO,TRIMESTRE,MES,EMPRESA,SERVICIO,EMPAQUETADO,TIPOLOGIA,MEDIO_ATENCION);

-- Agrupado de Quejas
CREATE MULTISET TABLE MAD.AY_CRC_QUEJAS_AGRUP AS (
SELECT
ANNO,       
TRIMESTRE,
MES,
EMPRESA,
SUM(NUMERO_QUEJAS) AS QUEJAS_TOTALES
FROM MAD.AY_CRC_QUEJAS_DETALLE
GROUP BY 1,2,3,4
) WITH DATA PRIMARY INDEX (ANNO,TRIMESTRE,MES,EMPRESA);

HELP TABLE mad.ay_crc_portados;
HELP TABLE MAD.AY_CRC_QUEJAS_AGRUP;

-- Promedio y Desviación de Quejas
CREATE MULTISET TABLE MAD.AY_CRC_QUEJAS_PROM_DESV AS (
SELECT
EMPRESA,
AVG(QUEJAS_TOTALES) AS PROM_QUEJAS,
STDDEV_POP(QUEJAS_TOTALES) AS DESV_QUEJAS
FROM MAD.AY_CRC_QUEJAS_AGRUP
GROUP BY 1
) WITH DATA PRIMARY INDEX (EMPRESA);

--Promedio y Desviación de Portabilidad
CREATE MULTISET TABLE MAD.AY_CRC_PORTA_PROM_DESV AS (
SELECT
EMPRESA,
AVG(PORTADOS_IN) AS PROM_PORTA_IN,
STDDEV_POP(PORTADOS_IN) AS DESV_PORTA_IN,
AVG(PORTADOS_OUT) AS PROM_PORTA_OUT,
STDDEV_POP(PORTADOS_OUT) AS DESV_PORTAOUT
FROM MAD.ay_crc_portados
GROUP BY 1
) WITH DATA PRIMARY INDEX (EMPRESA);

--Cración de índices
SELECT
A.*,
B.QUEJAS_TOTALES,
MAX((B.QUEJAS_TOTALES-C.PROM_QUEJAS)/C.DESV_QUEJAS) AS IND_QUEJAS,
MAX((A.PORTADOS_IN-D.PROM_PORTA_IN)/D.DESV_PORTA_IN) AS IND_PORTA_IN,
MAX((A.PORTADOS_OUT-D.PROM_PORTA_OUT)/D.DESV_PORTAOUT) AS IND_PORTA_OUT
FROM mad.ay_crc_portados AS A
LEFT JOIN
MAD.AY_CRC_QUEJAS_AGRUP AS B
ON A.ANNO=B.ANNO AND A.TRIMESTRE=B.TRIMESTRE AND A.MES=B.MES AND A.EMPRESA=B.EMPRESA
LEFT JOIN
MAD.AY_CRC_QUEJAS_PROM_DESV AS C
ON A.EMPRESA=C.EMPRESA
LEFT JOIN
MAD.AY_CRC_PORTA_PROM_DESV AS D
ON A.EMPRESA=D.EMPRESA
WHERE B.QUEJAS_TOTALES IS NOT NULL
GROUP BY 1,2,3,4,5,6,7;









