DROP TABLE TMP_TRAFEGO_VOZ_202408;
CREATE TABLE TMP_TRAFEGO_VOZ_202408 AS -- ATUALIZAR DATA
SELECT TRAF.NUM_TEL_ASS_VISIT  AS NUM_NTC,
       G.PERIODO,
       G.DW_NUM_NTC, 
       SUM(TRAF.DUR_LIGACAO)   AS DUR_LIGACAO
FROM DWH.BI_FP_TRAFEGO                TRAF
INNER JOIN POS_VR     G    ON G.NUM_NTC = TRAF.NUM_TEL_ASS_VISIT
AND   TRAF.COD_TIPO_LIGACAO_GERENCIAL IN ('VC1F', 'VC1I', 'VC1M', 'VC2F', 'VC2I', 'VC2M', 'VC3F', 'VC3I','VC3M') 
AND   TRAF.DAT_LIGACAO BETWEEN TO_DATE('01/08/2024','DD/MM/RRRR') AND TO_DATE('07/09/2024','DD/MM/RRRR') -- ATUALIZAR M e M+1
AND   TRAF.DAT_LIGACAO >= G.DAT_MOVIMENTO 
AND   TRAF.DAT_LIGACAO <= (G.DAT_MOVIMENTO + 6)
AND   G.DAT_MOVIMENTO BETWEEN '01/08/2024' AND '31/08/2024'
GROUP BY TRAF.NUM_TEL_ASS_VISIT, G.PERIODO, G.DW_NUM_NTC;
SELECT * FROM TMP_TRAFEGO_VOZ_202408;

DROP TABLE TMP_TRAFEGO_DADOS_202408;
CREATE TABLE TMP_TRAFEGO_DADOS_202408 AS -- ATUALIZAR DATA
SELECT TRAF.NUM_TEL_ASS_VISIT           AS NUM_NTC,
       G.PERIODO,
       G.DW_NUM_NTC,      
       ROUND(SUM(NVL(TRAF.QTD_BYTE_TOTAL,0) / 1024 / 1024), 2) AS MB
FROM DWH.BI_FP_TRAFEGO_GPRS_DETALHADO TRAF 
INNER JOIN POS_VR       G    ON G.NUM_NTC = TRAF.NUM_TEL_ASS_VISIT
AND   TRAF.DAT_SESSAO >= G.DAT_MOVIMENTO 
AND   TRAF.DAT_SESSAO <= (G.DAT_MOVIMENTO  + 6) 
AND   TRAF.DAT_SESSAO BETWEEN TO_DATE('01/08/2024','DD/MM/RRRR') AND TO_DATE('07/09/2024','DD/MM/RRRR') -- ATUALIZAR M e M+1
AND   TRAF.COD_TIPO_AGREGACAO in('B','NC')
AND   G.DAT_MOVIMENTO  BETWEEN '01/08/2024' AND '31/08/2024'
GROUP BY TRAF.NUM_TEL_ASS_VISIT, G.PERIODO, G.DW_NUM_NTC;
SELECT * FROM TMP_TRAFEGO_DADOS_202408;

SELECT * FROM TMP_TRAFEGO_DADOS_202408;
SELECT * FROM TMP_TRAFEGO_VOZ_202408;


-------------------------------------------------------- PREPAGO ---------------------------------------------
----------------------------------------------- TRAF VOZ ----------------------------------------------------
DROP TABLE TMP_TRAFEGO_VOZ_PRE_202408;
CREATE TABLE TMP_TRAFEGO_VOZ_PRE_202408 AS -- ATUALIZAR DATA
SELECT TRAF.NUM_TEL_ASS_VISIT  AS NUM_NTC,
       G.PERIODO,
       SUM(TRAF.DUR_LIGACAO)   AS DUR_LIGACAO
FROM DWH.BI_FP_TRAFEGO                TRAF
INNER JOIN PRE_VAREJO     G    ON G.NUM_NTC = TRAF.NUM_TEL_ASS_VISIT
AND   TRAF.COD_TIPO_LIGACAO_GERENCIAL IN ('VC1F', 'VC1I', 'VC1M', 'VC2F', 'VC2I', 'VC2M', 'VC3F', 'VC3I','VC3M') 
AND   TRAF.DAT_LIGACAO BETWEEN TO_DATE('01/08/2024','DD/MM/RRRR') AND TO_DATE('07/09/2024','DD/MM/RRRR') -- ATUALIZAR M e M+1
AND   TRAF.DAT_LIGACAO >= G.DAT_MOVIMENTO 
AND   TRAF.DAT_LIGACAO <= (G.DAT_MOVIMENTO + 6)
AND   G.DAT_MOVIMENTO BETWEEN '01/08/2024' AND '31/08/2024'
GROUP BY TRAF.NUM_TEL_ASS_VISIT, G.PERIODO;
SELECT * FROM TMP_TRAFEGO_VOZ_PRE_202408;

----------------------------------------------- TRAF DADOS ----------------------------------------------------

DROP TABLE TMP_TRAFEGO_DADOS_PRE_202408;
CREATE TABLE TMP_TRAFEGO_DADOS_PRE_202408 AS -- ATUALIZAR DATA
SELECT TRAF.NUM_TEL_ASS_VISIT           AS NUM_NTC,
       G.PERIODO,      
       ROUND(SUM(NVL(TRAF.QTD_BYTE_TOTAL,0) / 1024 / 1024), 2) AS MB
FROM DWH.BI_FP_TRAFEGO_GPRS_DETALHADO TRAF 
INNER JOIN PRE_VAREJO       G    ON G.NUM_NTC = TRAF.NUM_TEL_ASS_VISIT
AND   TRAF.DAT_SESSAO >= G.DAT_MOVIMENTO 
AND   TRAF.DAT_SESSAO <= (G.DAT_MOVIMENTO  + 6) 
AND   TRAF.DAT_SESSAO BETWEEN TO_DATE('01/08/2024','DD/MM/RRRR') AND TO_DATE('07/09/2024','DD/MM/RRRR') -- ATUALIZAR M e M+1
AND   TRAF.COD_TIPO_AGREGACAO in('B','NC')
AND   G.DAT_MOVIMENTO  BETWEEN '01/08/2024' AND '31/08/2024'
GROUP BY TRAF.NUM_TEL_ASS_VISIT, G.PERIODO;
SELECT * FROM TMP_TRAFEGO_DADOS_PRE_202408;

SELECT * FROM TMP_TRAFEGO_DADOS_PRE_202408;

------------------------------- Trafego Damaris ------------------------------------------------
/*
SELECT

P.PERIODO,
P.NUM_NTC,
P.REGIONAL,
P.DDD,
P.DAT_MOVIMENTO,
COUNT(P.NUM_NTC) AS QT,
CASE WHEN P.MB IS NULL THEN 0 ELSE P.MB END TRAF_DADOS,
CASE WHEN P.VOZ IS NULL THEN 0 ELSE P.VOZ END TRAF_VOZ,
CASE WHEN P.MB > 1 OR P.VOZ > 5 THEN 'S' ELSE 'N' END TRAFEGO

FROM PRE_VZ P

WHERE P.PERIODO = '202010'

GROUP BY

P.PERIODO,
P.NUM_NTC,
P.REGIONAL,
P.DDD,
P.DAT_MOVIMENTO,
P.MB,
P.VOZ
;

*/
