Apresentação sobre DW
- Fábio Ramos dos Santos
- DataWareShouse
- Instruções SQL
- BI
- DW
- Exemplos Consultas:
SELECT /*+ PARALLEL(CA,4) PARALLEL(AG,4) PARALLEL(IT,4) PARALLEL(CUS,4)*/
ca.ctbcprotocolo "Protocolo",
decode(upper(cst.Short_Desc), 'CANCELAMENTO', 'CANCELADO', upper(cst.Short_Desc)) "Status Contrato",
cus.name "Cliente",
lo.ctbcnomecnl "Localidade",
substr(lo.ctbcddd,2,2) "DDD",
case
when (decode(upper(cst.Short_Desc), 'CANCELAMENTO', 'CANCELADO', upper(cst.Short_Desc))) = 'CANCELADO'
then null
else substr(ag.phone_number,3)
end "Telefone",
to_char(ca.swdatecreated, 'dd/mm/yyyy') "Data Abertura",
to_char(ca.swdatecreated, 'hh24:mi:ss') "Hora Abertura",
sot.description1 "Motivo1",
sot.description2 "Motivo2",
sot.description3 "Motivo3",
sot.description4 "Motivo4",
ca.ctbcnomeinbox "inbox",
decode (cus.flag_prog_ligacao, 1, 'LIGACAO',
2, 'LIGACAO EMPRESAS',
3, 'LIGACAO EMPRESAS (PRATA)',
4, 'LIGACAO/LIGACAO VITAL',
5, 'LIGACAO VITAL',
6, 'LIGACAO EXPANSAO VAREJO') "Cliente Ligacao",
to_char(ca.ctbcdatafechamento, 'dd/mm/yyyy') "Data Fechamento",
to_char(ca.ctbcdatafechamento, 'hh24:mi:ss') "Hora Fechamento",
to_char(ca.ctbcfechadoimprocedeem, 'dd/mm/yyyy') "Data Fech Improc",
to_char(ca.ctbcfechadoimprocedeem, 'hh24:mi:ss') "Hora Fech Improc",
TRUNC(TRUNC(ca.ctbcdatafechamento) - TRUNC(ca.swdatecreated)) "Idade",
substr(lo.ctbcddd,2,2) "Área",
ca.swstatus "Status Caso",
to_char(CA.Swcaseid) "ID Caso", -- ID do Caso
CASE
WHEN (ca.swdatecreated >= TO_DATE('&datainicio' || ' 00:00:00','DD/MM/RRRR HH24:MI:SS')
AND ca.swdatecreated <= TO_DATE('&datafim' || ' 23:59:59','DD/MM/RRRR HH24:MI:SS'))
-- OR (ca.ctbcdatafechamento >= TO_DATE('&datainicio' || ' 00:00:00','DD/MM/RRRR HH24:MI:SS')
-- AND ca.ctbcdatafechamento <= TO_DATE('&datafim' || ' 23:59:59','DD/MM/RRRR HH24:MI:SS') + 5)
THEN 1
ELSE 0
END do_mes,
CASE
WHEN (ca.swdatecreated >= TO_DATE('&datainicio' || ' 00:00:00','DD/MM/RRRR HH24:MI:SS')
AND ca.swdatecreated <= TO_DATE('&datafim' || ' 23:59:59','DD/MM/RRRR HH24:MI:SS')
and TRUNC(TRUNC(ca.ctbcdatafechamento) - TRUNC(ca.swdatecreated)) <= 5)
THEN 1
ELSE 0
END ate_5dias,
CASE
WHEN TRUNC(TRUNC(ca.ctbcdatafechamento) - TRUNC(ca.swdatecreated)) > 5 AND
TRUNC(TRUNC(ca.ctbcdatafechamento) - TRUNC(ca.swdatecreated)) <= 10
THEN 1
ELSE 0
END ate_10dias,
CASE
WHEN (ca.swdatecreated >= TO_DATE('&datainicio' || ' 00:00:00','DD/MM/RRRR HH24:MI:SS')
AND ca.swdatecreated <= TO_DATE('&datafim' || ' 23:59:59','DD/MM/RRRR HH24:MI:SS')
and TRUNC(TRUNC(ca.ctbcdatafechamento) - TRUNC(ca.swdatecreated)) > 10)
THEN 1
ELSE 0
END acima_10dias,
CASE
WHEN (ca.swdatecreated >= TO_DATE('&datainicio' || ' 00:00:00','DD/MM/RRRR HH24:MI:SS')
AND ca.swdatecreated <= TO_DATE('&datafim' || ' 23:59:59','DD/MM/RRRR HH24:MI:SS')
and (CA.Swagreementid) > 0
and ca.swstatus in ('ABERTO', 'SERVICO DESPACHADO', 'ABERTO COM OCORRENCIA'))
THEN 1
ELSE 0
END em_aberto_periodo,
CASE
WHEN (ca.swdatecreated < TO_DATE('&datainicio' || ' 00:00:00','DD/MM/RRRR HH24:MI:SS')
and ca.swdatecreated > (TO_DATE('&datainicio' || ' 00:00:00','DD/MM/RRRR HH24:MI:SS') - 30)
and (CA.Swagreementid) > 0
and ca.swstatus in ('ABERTO', 'SERVICO DESPACHADO', 'ABERTO COM OCORRENCIA'))
THEN 1
ELSE 0
END pendentes_mes_anterior,
concessao_autorizacao
FROM
CA,
AG,
LO,
IT,
SOT,
CST,
CUS
WHERE
to_char(CA.Swagreementid) = AG.SRC_SYS_KEY
and ag.contract_cnl = lo.ctbccodcnl
and CA.Ctbcinteracaoid = IT.SRC_SYS_KEY
and nvl(CA.Ctbcmotivo1,0) = SOT.Src_Sys_Key1
and nvl(CA.Ctbcmotivo2,0) = SOT.Src_Sys_Key2
and nvl(CA.Ctbcmotivo3,0) = SOT.Src_Sys_Key3
and nvl(CA.Ctbcmotivo4,0) = SOT.Src_Sys_Key4
and ag.CONTRACT_STATUS_KEY = cst.contract_status_key
and ag.customer_key = CUS.customer_key
and (
/* casos (procedentes ou não) que foram fechados no período e criados fora deste período */
(ca.ctbcdatafechamento >= TO_DATE('&datainicio' || ' 00:00:00','DD/MM/RRRR HH24:MI:SS')
and ca.ctbcdatafechamento <= TO_DATE('&datafim' || ' 23:59:59','DD/MM/RRRR HH24:MI:SS')
and (ca.swdatecreated < TO_DATE('&datainicio' || ' 00:00:00','DD/MM/RRRR HH24:MI:SS')
OR ca.swdatecreated > TO_DATE('&datafim' || ' 23:59:59','DD/MM/RRRR HH24:MI:SS'))
and (ca.swstatus = 'FECHADO'
or ca.swstatus = 'FECHADO IMPROCEDENTE')
)
or
/* casos (procedentes ou não) que foram criados dentro do período e foram fechados dentro do período */
(ca.swdatecreated >= TO_DATE('&datainicio' || ' 00:00:00','DD/MM/RRRR HH24:MI:SS')
AND ca.swdatecreated <= TO_DATE('&datafim' || ' 23:59:59','DD/MM/RRRR HH24:MI:SS')
AND ca.ctbcdatafechamento >= TO_DATE('&datainicio' || ' 00:00:00','DD/MM/RRRR HH24:MI:SS')
AND ca.ctbcdatafechamento <= (TO_DATE('&datafim' || ' 23:59:59','DD/MM/RRRR HH24:MI:SS') + 5)
and (ca.swstatus = 'FECHADO'
or ca.swstatus = 'FECHADO IMPROCEDENTE')
)
or
/* casos abertos */
(ca.swdatecreated <= TO_DATE('&datafim' || ' 23:59:59','DD/MM/RRRR HH24:MI:SS')
and ca.swdatecreated > (TO_DATE('&datainicio' || ' 00:00:00','DD/MM/RRRR HH24:MI:SS') - 30)
and (CA.Swagreementid) > 0
and ca.swstatus in ('ABERTO', 'SERVICO DESPACHADO', 'ABERTO COM OCORRENCIA')
)
)
and ca.swdatecreated > TO_DATE('1/1/2006 00:00:00','DD/MM/RRRR HH24:MI:SS')
and ag.record_status = 1
--and ag.contract_type = 'TELEFONE MOVEL GSM'
--and ca.line_of_business_key = 5
and ca.swproductlineid = 22
and sot.record_status = 1
and sot.src_sys_name = 'VANTIVE'
and cst.record_status = 1
and cst.src_sys_name = 'VANTIVE'
and cus.record_status = 1
and cus.src_sys_name = 'VANTIVE'
AND SOT.SRC_SYS_KEY1 || null IN ('646' /*SOLICITACAO*/)
and (Instr(';PLAT_PRE_GSM;', ';'|| ca.swcreatedby ||';')) = 0
and (cus.flag_prog_ligacao <> 6
or cus.flag_prog_ligacao is null)