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 ( 4 months 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