Welcome, guest! Login / Register - Why register?
Psst.. new poll here.
[email protected] webmail now available. Want one? Go here.
Cannot use outlook/hotmail/live here to register as they blocking our mail servers. #microsoftdeez
Obey the Epel!

Paste

Pasted as SQL by registered user vvillacorta ( 11 months ago )
SELECT gestion, num_ruc, num_telefono, ruc_telefono,
                 MAX(CASE WHEN orden_este_mes = 1 THEN fec_llamada else NULL END) fec_llamada,
                 MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) categoria_actual,
                 MAX(CASE WHEN orden_desde_actual = 1 THEN categoria else NULL END) categoria_desde_actual_u18m,
                 MAX(CASE WHEN gestion_previa < gestion THEN gestion_previa ELSE NULL END) ultima_gestion,
                 MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) categoria_best_previa,
                 CASE WHEN MAX(flg_conocido_u18m) = 1 then 1 else 0 end flg_tlf_ya_llamado_u18m,
                 CASE WHEN MAX(flg_conocido_u18m) = 0 then 1 else 0 end flg_tlf_fresco_u18m,
                 CASE WHEN MAX(flg_conocido_1m) = 1 then 1 else 0 end flg_tlf_recurrente_u1m,
                 CASE WHEN MAX(flg_conocido_1m) = 0 then 1 else 0 end flg_tlf_fresco_u1,
                 MAX(CASE WHEN orden_previo = 1 then diff_meses else NULL END) recencia_best,
                 MAX(CASE WHEN orden_previo = 1 and categoria = 'CE' then diff_meses else NULL END) recencia_ce,
                 CASE WHEN MAX(gestion_previa) IS NOT NULL AND MAX(gestion_previa) < MAX(gestion) THEN 1 ELSE 0 END flg_tlf_desaparecido,
                 CASE WHEN MAX(gestion_previa) IS NOT NULL AND MAX(gestion_previa) < MAX(gestion) AND MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = 'ET' THEN 1 ELSE 0 END flg_tlf_desaparecido_x_et,
                 CASE WHEN MAX(gestion_previa) IS NOT NULL AND MAX(gestion_previa) < MAX(gestion) AND MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = 'CE' THEN 1 ELSE 0 END flg_tlf_desaparecido_era_ce,
                 CASE WHEN MAX(gestion_previa) < MAX(gestion) AND MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) != 'ET' THEN 1 ELSE 0 END flg_tlf_desaparecido_no_et,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = 'CE' THEN 1 ELSE 0 END flg_fue_ce,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = 'NC' THEN 1 ELSE 0 END flg_fue_nc,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = 'CE' AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) = 'CE' then 1 ELSE 0 END flg_fue_ce_ahora_ce,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = 'CE' AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) = 'NC' then 1 ELSE 0 END flg_fue_ce_ahora_nc,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = 'CE' AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) = 'ET' then 1 ELSE 0 END flg_fue_ce_ahora_et,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = 'CE' AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) in ('ET', 'NC') then 1 ELSE 0 END flg_fue_ce_ahora_et_nc,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = 'NC' AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) = 'CE' then 1 ELSE 0 END flg_fue_nc_ahora_ce,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = 'NC' AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) = 'NC' then 1 ELSE 0 END flg_fue_nc_ahora_nc,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = 'NC' AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) = 'ET' then 1 ELSE 0 END flg_fue_nc_ahora_et,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = 'NC' AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) in ('ET', 'NC') then 1 ELSE 0 END flg_fue_nc_ahora_et_nc,                 
                 CASE WHEN MAX(flg_conocido_u18m) = 0 AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) = 'CE' then 1 ELSE 0 END flg_fresco_u18m_ahora_ce,
                 CASE WHEN MAX(flg_conocido_u18m) = 0 AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) = 'NC' then 1 ELSE 0 END flg_fresco_u18m_ahora_nc,
                 CASE WHEN MAX(flg_conocido_u18m) = 0 AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) = 'ET' then 1 ELSE 0 END flg_fresco_u18m_ahora_et,
                 CASE WHEN MAX(flg_conocido_u18m) = 0 AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) in ('ET', 'NC') then 1 ELSE 0 END flg_fresco_u18m_ahora_et_nc,
                 gestion p_periodo
          FROM(
          SELECT AA.gestion, AA.num_ruc, BB.num_telefono, bb.ruc_telefono, bb.gestion gestion_previa, bb.categoria, bb.fec_llamada,
                 ROW_NUMBER() OVER(PARTITION BY AA.gestion, AA.num_ruc, BB.num_telefono ORDER BY BB.PESO ASC, fec_llamada DESC) orden_desde_actual,
                 CASE WHEN AA.gestion > BB.gestion THEN 
                      ROW_NUMBER() OVER(PARTITION BY AA.gestion, BB.num_ruc, BB.num_telefono, (case when AA.gestion > BB.gestion then 1 else 0 end) 
                                        ORDER BY BB.PESO ASC, BB.gestion DESC) ELSE NULL END orden_previo,
                 CASE WHEN AA.gestion = BB.gestion THEN ROW_NUMBER() OVER(PARTITION BY AA.gestion, BB.gestion, BB.num_ruc, BB.num_telefono ORDER BY BB.PESO ASC,fec_llamada DESC) ELSE NULL END orden_este_mes,
                 CASE WHEN BB.gestion = SUBSTRING(REPLACE(cast(date_add('month', -1, cast(date_parse(AA.gestion,'%Y%m') as date)) as varchar), '-', ''), 1, 6) then 1 else 0 end flg_conocido_1m,
                 CASE WHEN AA.gestion > BB.gestion and BB.gestion is not null then 1 else 0 end flg_conocido_u18m,
                 CASE WHEN AA.gestion > BB.gestion and BB.gestion is not null THEN ABS(
                     (CAST(SUBSTRING(AA.gestion,1,4) AS INT)* 18 + CAST(SUBSTRING(AA.gestion,5,2) AS INT)) -
                     (CAST(SUBSTRING(BB.gestion,1,4) AS INT)* 18 + CAST(SUBSTRING(BB.gestion,5,2) AS INT)) 
                 )  ELSE NULL END diff_meses
          FROM d_mdl_vpc_disc.hm_periodo_ruc_sg AA
          LEFT JOIN d_mdl_vpc_disc.HM_PERIODO_RUC_TELEFONO_SG BB
          ON AA.gestion >= BB.gestion
          AND SUBSTRING(REPLACE(cast(date_add('month', -18, cast(date_parse(AA.gestion,'%Y%m') as date)) as varchar), '-', ''), 1, 6) <= BB.gestion
          AND AA.num_ruc = BB.num_ruc
          --WHERE AA.num_ruc like 'A702AB%'
          --AND bb.num_telefono = '943467478'
          --ORDER BY AA.gestion DESC, bb.num_telefono, bb.gestion DESC, AA.num_ruc, bb.fec_llamada
          ) YY
          GROUP BY gestion, num_ruc, num_telefono, ruc_telefono
          ORDER BY gestion DESC, num_ruc, case when recencia_ce is null then 999 else recencia_ce end ASC

 

Revise this Paste

Your Name: Code Language: