Paste code

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 = &#039;CE&#039; 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) = &#039;ET&#039; 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) = &#039;CE&#039; 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) != &#039;ET&#039; THEN 1 ELSE 0 END flg_tlf_desaparecido_no_et,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = &#039;CE&#039; THEN 1 ELSE 0 END flg_fue_ce,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = &#039;NC&#039; THEN 1 ELSE 0 END flg_fue_nc,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = &#039;CE&#039; AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) = &#039;CE&#039; then 1 ELSE 0 END flg_fue_ce_ahora_ce,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = &#039;CE&#039; AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) = &#039;NC&#039; then 1 ELSE 0 END flg_fue_ce_ahora_nc,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = &#039;CE&#039; AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) = &#039;ET&#039; then 1 ELSE 0 END flg_fue_ce_ahora_et,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = &#039;CE&#039; AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) in (&#039;ET&#039;, &#039;NC&#039;) then 1 ELSE 0 END flg_fue_ce_ahora_et_nc,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = &#039;NC&#039; AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) = &#039;CE&#039; then 1 ELSE 0 END flg_fue_nc_ahora_ce,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = &#039;NC&#039; AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) = &#039;NC&#039; then 1 ELSE 0 END flg_fue_nc_ahora_nc,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = &#039;NC&#039; AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) = &#039;ET&#039; then 1 ELSE 0 END flg_fue_nc_ahora_et,
                 CASE WHEN MAX(CASE WHEN orden_previo = 1 then categoria else NULL END) = &#039;NC&#039; AND MAX(CASE WHEN orden_este_mes = 1 THEN categoria else NULL END) in (&#039;ET&#039;, &#039;NC&#039;) 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) = &#039;CE&#039; 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) = &#039;NC&#039; 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) = &#039;ET&#039; 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 (&#039;ET&#039;, &#039;NC&#039;) 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(&#039;month&#039;, -1, cast(date_parse(AA.gestion,&#039;%Y%m&#039;) as date)) as varchar), &#039;-&#039;, &#039;&#039;), 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(&#039;month&#039;, -18, cast(date_parse(AA.gestion,&#039;%Y%m&#039;) as date)) as varchar), &#039;-&#039;, &#039;&#039;), 1, 6) <= BB.gestion
          AND AA.num_ruc = BB.num_ruc
          --WHERE AA.num_ruc like &#039;A702AB%&#039;
          --AND bb.num_telefono = &#039;943467478&#039;
          --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