my code stock.com

marcus.sacramento

QR EXPURGO DIÁRIO
by marcus.sacramento

Para expurgo diário de ARS's a partir do dia 23/02/2012

Snippet options

Download: Download snippet as qr-expurgo-diario.sql.
Copy snippet: For this you need a free my code stock.com account.
Embed code : You will find the embed code for this snippet at the end of the page, if you want to embed it into a website or a blog!

SELECT   B.ARS,
              (SELECT   C536870990  FROM  t90 WHERE  c1 = B.ARS) AS AGENTE_SOLUCAO_ATUAL,
              (SELECT   C536873222 FROM  t90 WHERE  c1 = B.ARS) AS SEVERIDADE,
   (CASE
                    WHEN (B.Agente = 'OPERACAO_INVESTIGACAO_PROB') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_COMBOS') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_ESCALONA_CLY_FIXA') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_ESCALONA_CLY_GSM') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_ESCALONA_EAI_FIXA') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_ESCALONA_OMS_FIXA') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_MIGRA_CLY_FIXA') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_N1_BDWEB_FIXA') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_N1_CLY_FIXA') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_N1_CLY_GSM') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_N1_FIX') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_N1_GECO') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_N2_CLY_GSM') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_N2_GENEVA') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_N2_MICROSIGA_GSM') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_PORTAB_ESCALONA_FIXA') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_PORTAB_ESCALONA_GSM') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_PORTAB_FIXA') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_PORTAB_GSM') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_PORTAB_PROCON_FIXA') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_PORTAB_PROCON_GSM') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_PROCON_CLY_FIXA') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_PROCON_CLY_GSM') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_SUP_GECO') THEN 'NAO'
                    WHEN (B.Agente = 'SUPORTE_CLARIFY_SMP_FIX') THEN 'NAO'
                    WHEN (B.Agente = 'SUPORTE_FIX_CLARIFY') THEN 'NAO'
                    WHEN (B.Agente = 'OPERACAO_INV_REVENDA_CLY_GSM') THEN 'NAO'
                    WHEN (B.Agente = 'SUPORTE_GECO_FIX') THEN 'NAO'
                    WHEN (B.Agente = 'SUPORTE_MIGRACAO_CRM_FIX') THEN 'NAO'
                    WHEN (B.Agente = 'SUPORTE_NOVO_DECRETO_CLARIFY') THEN 'NAO'
                    WHEN (B.Agente = 'SUPORTE_OUVIDORIA_CLARIFY') THEN 'NAO'
                    WHEN (B.Agente = 'SUPORTE_BACKLOG_CRM_FIX_FIXA') THEN 'NAO'
                    WHEN (B.Agente = 'SUPORTE_BACKLOG_CRM_FIX_GSM') THEN 'NAO'
                    WHEN (B.Agente = 'SUPORTE_BACKLOG_GECO_FIX') THEN 'NAO'
                    WHEN (B.Agente = 'SUPORTE_BD_WEB_FIX') THEN 'NAO'
                    WHEN (B.Agente = 'SUPORTE_CLARIFY_SERVICO') THEN 'NAO'
                    WHEN (B.Agente = 'SUPORTE_CRM_DEALER_FIX') THEN 'NAO'
                    WHEN (B.Agente = 'SUPORTE_SIS_CLY') THEN 'NAO'
                    ELSE 'SIM'
                END)
                  AS Expurgo, -- verifica se o agente pertence ao grupo de agente do CLY,se nao pertencer o expurgo e valido (SIM)
              B.Agente AS AGENTE_EXPURGO,
              B.tempo,
                 EXTRACT (DAY FROM NUMTODSINTERVAL (B.tempo / 60 / 24, 'DAY')) * 24
              + EXTRACT (HOUR FROM NUMTODSINTERVAL (B.tempo / 60 / 24, 'DAY'))
              || ':'
              || (EXTRACT (MINUTE FROM NUMTODSINTERVAL (B.tempo / 60 / 24, 'DAY'))
                    + ROUND (EXTRACT (SECOND FROM NUMTODSINTERVAL (B.tempo / 60 / 24,
                                                            'DAY'))
                                / 60))
                  AS Horas,
                  EXTRACT (DAY FROM NUMTODSINTERVAL (B.tempo / 60 / 24, 'DAY'))
              || ' Dia(s), '
              || EXTRACT (HOUR FROM NUMTODSINTERVAL (B.tempo / 60 / 24, 'DAY'))
              || ' Hora(s) e '
              || (EXTRACT (MINUTE FROM NUMTODSINTERVAL (B.tempo / 60 / 24, 'DAY'))
                    + ROUND (EXTRACT (SECOND FROM NUMTODSINTERVAL (B.tempo / 60 / 24,
                                                            'DAY'))
                                / 60))
              || ' Minutos(s)'
                  AS EXTENSO,
              (SELECT   TO_DATE ('31/12/1969 21:00:00', 'DD/MM/YYYY HH24:MI:SS')+ c3 / (24 * 60 * 60) FROM  t90 WHERE  c1 = B.ARS) AS DATA_ABERTURA,
              (SELECT   TO_DATE ('31/12/1969 21:00:00', 'DD/MM/YYYY HH24:MI:SS')+ C536870936 / (24 * 60 * 60) FROM  t90 WHERE  c1 = B.ARS) AS DATA_RESOLVIDO,
              (SELECT   TO_DATE ('31/12/1969 21:00:00', 'DD/MM/YYYY HH24:MI:SS')+ C536871433 / (24 * 60 * 60) FROM  t90 WHERE  c1 = B.ARS) AS DATA_FECHAMENTO,
              (SELECT   TO_DATE ('31/12/1969 21:00:00', 'DD/MM/YYYY HH24:MI:SS')+ C536871432 / (24 * 60 * 60) FROM  t90 WHERE  c1 = B.ARS) AS DATA_REABERTURA
     FROM   (SELECT A.ARS, A.agente, A.tempo
                  FROM  (   SELECT  C536870913 AS ARS, C536870990 AS agente,
                                            SUM (C536870915) AS tempo
                                  FROM  T92
                                 WHERE  C536870913 IN
                                                    (SELECT  C1
                                                        FROM     t90
                                                      WHERE  (C536870936 >=
                                                                      ( (TRUNC ( (SYSDATE - 8)) -- alterar (ultima data que foi feito o monitor)
                                                                          - TO_DATE ('31/12/1969 21:00:00',
                                                                             'DD/MM/YYYY HH24:MI:SS'))
                                                                        * 86400)
                                                                  AND (C536870936 <
                                                                             ( (TRUNC (SYSDATE - 7) -- alterar
                                                                                 - TO_DATE ('31/12/1969 21:00:00',
                                                                                    'DD/MM/YYYY HH24:MI:SS'))
                                                                              * 86400)))
                                                                 AND C536870990 IN
                                                                             ('OPERACAO_INVESTIGACAO_PROB',
                                                                              'OPERACAO_INV_COMBOS',
                                                                              'OPERACAO_INV_ESCALONA_CLY_FIXA',
                                                                              'OPERACAO_INV_ESCALONA_CLY_GSM',
                                                                              'OPERACAO_INV_ESCALONA_EAI_FIXA',
                                                                              'OPERACAO_INV_ESCALONA_OMS_FIXA',
                                                                              'OPERACAO_INV_MIGRA_CLY_FIXA',
                                                                              'OPERACAO_INV_N1_BDWEB_FIXA',
                                                                              'OPERACAO_INV_N1_CLY_FIXA',
                                                                              'OPERACAO_INV_N1_CLY_GSM',
                                                                              'OPERACAO_INV_N1_FIX',
                                                                              'OPERACAO_INV_N1_GECO',
                                                                              'OPERACAO_INV_N2_CLY_GSM',
                                                                              'OPERACAO_INV_N2_GENEVA',
                                                                              'OPERACAO_INV_N2_MICROSIGA_GSM',
                                                                              'OPERACAO_INV_PORTAB_ESCALONA_FIXA',
                                                                              'OPERACAO_INV_PORTAB_ESCALONA_GSM',
                                                                              'OPERACAO_INV_PORTAB_FIXA',
                                                                              'OPERACAO_INV_PORTAB_GSM',
                                                                              'OPERACAO_INV_PORTAB_PROCON_FIXA',
                                                                              'OPERACAO_INV_PORTAB_PROCON_GSM',
                                                                              'OPERACAO_INV_PROCON_CLY_FIXA',
                                                                              'OPERACAO_INV_PROCON_CLY_GSM',
                                                                              'OPERACAO_SUP_GECO',
                                                                              'SUPORTE_CLARIFY_SMP_FIX',
                                                                              'SUPORTE_FIX_CLARIFY',
                                                                              'OPERACAO_INV_REVENDA_CLY_GSM',
                                                                              'SUPORTE_GECO_FIX',
                                                                              'SUPORTE_MIGRACAO_CRM_FIX',
                                                                              'SUPORTE_NOVO_DECRETO_CLARIFY',
                                                                              'SUPORTE_OUVIDORIA_CLARIFY',
                                                                              'SUPORTE_BACKLOG_CRM_FIX_FIXA',
                                                                              'SUPORTE_BACKLOG_CRM_FIX_GSM',
                                                                              'SUPORTE_BACKLOG_GECO_FIX',
                                                                              'SUPORTE_BD_WEB_FIX',
                                                                              'SUPORTE_CLARIFY_SERVICO',
                                                                              'SUPORTE_CRM_DEALER_FIX',
                                                                              'SUPORTE_SIS_CLY')
                                                                 AND c7 IN (8, 9)) -- filtra por ARS
                                            AND C536870914 IN (1, 2, 4, 5) -- status a ser levando em conta nos minutos acumulados por agente (1,2,4 ou 5)
                             GROUP BY   C536870913, C536870990
                             ORDER BY   SUM (C536870915) DESC) A) B
ORDER BY   b.ARS, b.tempo DESC;

Create a free my code stock.com account now.

my code stok.com is a free service, which allows you to save and manage code snippes of any kind and programming language. We provide many advantages for your daily work with code-snippets, also for your teamwork. Give it a try!

Find out more and register now

You can customize the height of iFrame-Codes as needed! You can find more infos in our API Reference for iframe Embeds.