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 substr(replace(cast(fecha_desemb_dt as varchar), '-', ''), 1, 6) periodo_desembolso,
substr(replace(cast(fecha_solicitud_dt as varchar), '-', ''), 1, 6) periodo_solicitud,
numero_documento_val,
max(CASE WHEN colocacion_neta > 0 then 1 else 0 end) flg_desembolso
FROM (
select ZZ.*,
ROW_NUMBER() OVER(PARTITION BY ZZ.credito_cd ORDER BY ZZ.fecha_solicitud_dt ASC, ZZ.periodo_val ASC, ZZ.fecinformacion_dt ASC) ORDEN
FROM (
SELECT CC.*,
CASE WHEN CC.campana_val like '%Recurrente%' and GG.flg_recurrente = 0 AND flg_nuevos_ap = 1 then 1 else 0 end flg_rectificado_nuevos_ap,
CASE WHEN CC.campana_val like '%Recurrente%' and GG.flg_recurrente = 0 AND flg_nuevos_pre = 1 then 1 else 0 end flg_rectificado_nuevos_pre
FROM e_perm_aws.t_fact_vpc_colnetas CC
LEFT JOIN (
SELECT gestion , num_ruc_autocompletado,
MAX(CASE WHEN campania = 'RECURRENTE' then 1 else 0 end) flg_recurrente,
MAX(CASE WHEN campania = 'NUEVOS PRE' OR lower(nom_lista) like '%dig_c%' then 1 else 0 end) flg_nuevos_pre,
MAX(CASE WHEN campania in ('NUEVOS AP', 'ECOSISTEMAS') OR lower(nom_lista) like '%dig_b%' then 1 else 0 end) flg_nuevos_ap,
SUM(FLG_GESTIONADO_ESTRICTO) gestion_total,
SUM(flg_ce) ce_total,
SUM(flg_acepta_campana) ac_total
FROM e_perm_aws.ds_csfedtlv_pso
WHERE 1 = 1
AND (
flg_bpe = 1
OR lower(nom_lista) like '%bpe%'
)
AND (
CAMPANIA IN ('NUEVOS AP', 'ECOSISTEMAS', 'NUEVOS PRE', 'RECURRENTE')
OR lower(nom_lista) like '%dig_c%'
OR lower(nom_lista) like '%dig_b%'
)
AND flg_gestionado_estricto = 1
GROUP BY gestion , num_ruc_autocompletado
) GG
ON substr(replace(cast(CC.fecha_solicitud_dt as varchar), '-', ''), 1, 6) = GG.gestion
AND CC.numero_documento_val = GG.num_ruc_autocompletado
WHERE 1 = 1
AND CC.colocacion_neta > 0
AND CC.tipo_colocacion NOT LIKE '%Cima%'
AND (
CC.tipo_colocacion in ('1. Campaña Nuevos', '103.Ecosistemas', '5. Campaña Express', '4. EN – Express') OR
lower(CC.campana_val) like '%nuevos pre%' OR lower(CC.campana_val) like '%nvo pre%' OR lower(CC.campana_val) like '%nvopre%' OR
lower(CC.campana_val) like '%nuevos ap%' OR lower(CC.campana_val) like '%nvo ap%' OR lower(CC.campana_val) like '%nvoap%' OR
(lower(CC.campana_val) like '%recurrente%' and GG.flg_recurrente = 0 AND GG.flg_nuevos_ap = 1) OR
(lower(CC.campana_val) like '%recurrente%' and GG.flg_recurrente = 0 AND GG.flg_nuevos_pre = 1) OR
lower(CC.campana_val) like '%nuevo pre%' OR
lower(CC.campana_val) like 'nuevo ap%' OR
CC.tipo_colocacion LIKE '%1.%' OR
CC.tipo_colocacion LIKE '%4.%' OR
CC.tipo_colocacion LIKE '%5.%' OR
CC.tipo_colocacion LIKE '%103.%'
)
AND CC.fecinformacion_dt in (
select fecinformacion_dt from (
SELECT periodo_val,
max(fecinformacion_dt) fecinformacion_dt
FROM e_perm_aws.t_fact_vpc_colnetas
GROUP BY periodo_val
)
)
) ZZ
) AA
WHERE AA.ORDEN = 1
AND (
(lower(campana_val) like '%recurrente%' AND flg_rectificado_nuevos_pre = 1) OR
(lower(campana_val) not like '%recurrente%' AND (tipo_colocacion in ('1. Campaña Nuevos')
OR tipo_colocacion LIKE '%1.%'
OR lower(campana_val) like '%nuevo pre%'
OR lower(campana_val) like '%nuevos pre%'
OR lower(campana_val) like '%nvo pre%'
OR lower(campana_val) like '%nvos pre%'
OR lower(campana_val) like '%nvospre%'
OR lower(campana_val) like '%nvopre%')
) OR
(lower(campana_val) like '%recurrente%' AND flg_rectificado_nuevos_ap = 1) OR
(lower(campana_val) not like '%recurrente%' AND (tipo_colocacion in ( '103.Ecosistemas', '5. Campaña Express', '4. EN – Express')
OR tipo_colocacion LIKE '%103.%'
OR tipo_colocacion LIKE '%4.%'
OR tipo_colocacion LIKE '%5.%'
OR lower(campana_val) like '%nuevo ap%'
OR lower(campana_val) like '%nuevos ap%'
OR lower(campana_val) like '%nvo ap%'
OR lower(campana_val) like '%nvos ap%'
OR lower(campana_val) like '%nvosap%'
OR lower(campana_val) like '%nvoap%')
)
)
GROUP BY substr(replace(cast(fecha_desemb_dt as varchar), '-', ''), 1, 6),
substr(replace(cast(fecha_solicitud_dt as varchar), '-', ''), 1, 6),
numero_documento_val
Revise this Paste