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 ( 9 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