Psst.. new poll here.
Psst.. new forums here.
Microsoft is blocking us again (TY IP Reputation!) so dont bother with any of their useless mail servers here and just use oauth login instead. Thank the nice Russians for causing that. :)
Paste
Pasted as SQL by registered user vvillacorta ( 3 years 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