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 ( 4 years ago )
SELECT z.prestamo_id,
max(CASE WHEN estado_cuota IN ('PAID') THEN numero_cuota ELSE NULL END ) ult_nro_cuotas_pagadas,
max(CASE WHEN estado_cuota IN ('PAID') THEN Fecha_Pago ELSE NULL END ) ult_fecha_pago_limite,
max(case when numero_cuota in (1) then Fecha_Pago else NULL end) fecha_1er_pago_limite,
max(case when numero_cuota in (2) then Fecha_Pago else NULL end) fecha_2do_pago_limite,
max(case when numero_cuota in (3) then Fecha_Pago else NULL end) fecha_3er_pago_limite,
max(case when numero_cuota in (4) then Fecha_Pago else NULL end) fecha_4to_pago_limite,
max(case when numero_cuota in (5) then Fecha_Pago else NULL end) fecha_5to_pago_limite,
max(case when numero_cuota in (6) then Fecha_Pago else NULL end) fecha_6to_pago_limite,
max(CASE WHEN estado_cuota IN ('PAID') THEN Fecha_Efectiva_Pago ELSE NULL END) ult_fecha_pago_efectivo,
sum(coalesce(monto_recaudado_from_income, 0)) monto_recaudado_from_income,
sum(CASE WHEN estado_cuota IN ('PAID') THEN coalesce(monto_recaudado_from_income, 0) ELSE 0 END) monto_recaudado_from_income_pagado,
sum(CASE WHEN estado_cuota IN ('PENDING') THEN coalesce(monto_recaudado_from_income, 0) ELSE 0 END) monto_recaudado_from_income_pendiente,
CASE WHEN sum(CASE WHEN estado_cuota IN ('PENDING') THEN coalesce(monto_recaudado_from_income, 0) ELSE 0 END) <= 0 THEN 1 ELSE 0 END flg_validacion_pendiente_sin_reacudo,
sum(CASE WHEN estado_cuota IN ('PAID') THEN monto_cuota ELSE 0 END) monto_cuotas_pagado,
sum(CASE WHEN estado_cuota IN ('PENDING') THEN monto_cuota ELSE 0 END) monto_cuota_pendiente,
sum(monto_recaudado_from_income)*100.0 / sum(monto_cuota) porc_pago_pos,
CASE WHEN sum(CASE WHEN estado_cuota IN ('PENDING') THEN coalesce(monto_cuota,0) ELSE 0 END) = 0 THEN NULL
ELSE sum(CASE WHEN estado_cuota IN ('PENDING') THEN coalesce(monto_recaudado_from_income,0) ELSE 0 END )*100.0 / sum(CASE WHEN estado_cuota IN ('PENDING') THEN coalesce(monto_cuota,0) ELSE 0 END) end porc_pago_pos_pendiente,
case WHEN sum(CASE WHEN estado_cuota IN ('PAID') THEN coalesce(monto_cuota,0) ELSE 0 END) = 0 THEN NULL
ELSE 100 - sum(CASE WHEN estado_cuota IN ('PAID') THEN coalesce(monto_recaudado_from_income,0) ELSE 0 END) *100.0 / sum(CASE WHEN estado_cuota IN ('PAID') THEN coalesce(monto_cuota,0) ELSE 0 END) end porc_pago_ventanilla,
case WHEN sum(CASE WHEN estado_cuota IN ('PAID') THEN coalesce(monto_cuota,0) ELSE 0 END) = 0 THEN NULL
ELSE 100 - sum(case when numero_cuota in (1) AND estado_cuota IN ('PAID') then coalesce(monto_recaudado_from_income,0) else NULL end )*100.0 / sum(case when numero_cuota in (1) AND estado_cuota IN ('PAID') then coalesce(monto_cuota,0) else NULL end) end porc_pago_ventanilla_1era_cuota,
case WHEN sum(CASE WHEN estado_cuota IN ('PAID') THEN coalesce(monto_cuota,0) ELSE 0 END) = 0 THEN NULL
ELSE 100 - sum(case when numero_cuota in (1,2) AND estado_cuota IN ('PAID') then coalesce(monto_recaudado_from_income,0) else NULL end )*100.0 / sum(case when numero_cuota in (1,2) AND estado_cuota IN ('PAID') then coalesce(monto_cuota,0) else NULL end) end porc_pago_ventanilla_2_1eras_cuotas,
case WHEN sum(CASE WHEN estado_cuota IN ('PAID') THEN coalesce(monto_cuota,0) ELSE 0 END) = 0 THEN NULL
ELSE 100 - sum(case when numero_cuota in (1,2,3) AND estado_cuota IN ('PAID') then coalesce(monto_recaudado_from_income,0) else NULL end )*100.0 / sum(case when numero_cuota in (1,2,3) AND estado_cuota IN ('PAID') then coalesce(monto_cuota,0) else NULL end) end porc_pago_ventanilla_3_1eras_cuotas,
max(z.dias_expirados) max_atraso,
max(dias_expirados_pagado) max_atraso_pagado,
max(dias_expirados_pendiente) max_atraso_pendiente,
max(z.flg_cuota_con_atraso) flg_atraso,
max(z.flg_cuota_con_atraso_5_o_mas) flg_atraso_5,
max(z.flg_cuota_con_atraso_15_o_mas) flg_atraso_15,
max(z.flg_cuota_con_atraso_30_o_mas) flg_atraso_30,
max(z.flg_cuota_con_atraso_60_o_mas) flg_atraso_60,
max(z.flg_cuota_con_atraso_90_o_mas) flg_atraso_90,
CASE WHEN max(dias_expirados_pagado) > 0 THEN 1 ELSE 0 END flg_atraso_pagado,
CASE WHEN max(dias_expirados_pagado) >= 5 THEN 1 ELSE 0 END flg_atraso_5_pagado,
CASE WHEN max(dias_expirados_pagado) >= 15 THEN 1 ELSE 0 END flg_atraso_15_pagado,
CASE WHEN max(dias_expirados_pagado) >= 30 THEN 1 ELSE 0 END flg_atraso_30_pagado,
CASE WHEN max(dias_expirados_pagado) >= 60 THEN 1 ELSE 0 END flg_atraso_60_pagado,
CASE WHEN max(dias_expirados_pagado) >= 90 THEN 1 ELSE 0 END flg_atraso_90_pagado,
CASE WHEN max(dias_expirados_pendiente) > 0 THEN 1 ELSE 0 END flg_atraso_pendiente,
CASE WHEN max(dias_expirados_pendiente) >= 5 THEN 1 ELSE 0 END flg_atraso_5_pendiente,
CASE WHEN max(dias_expirados_pendiente) >= 15 THEN 1 ELSE 0 END flg_atraso_15_pendiente,
CASE WHEN max(dias_expirados_pendiente) >= 30 THEN 1 ELSE 0 END flg_atraso_30_pendiente,
CASE WHEN max(dias_expirados_pendiente) >= 60 THEN 1 ELSE 0 END flg_atraso_60_pendiente,
CASE WHEN max(dias_expirados_pendiente) >= 90 THEN 1 ELSE 0 END flg_atraso_90_pendiente,
max(case when numero_cuota in (1) then flg_cuota_con_atraso else 0 end) flg_atraso_pagado_1era_cuota,
max(case when numero_cuota in (1) then flg_cuota_con_atraso_5_o_mas else 0 end) flg_atraso_5_pagado_1era_cuota,
max(case when numero_cuota in (1) then flg_cuota_con_atraso_15_o_mas else 0 end) flg_atraso_15_pagado_1era_cuota,
max(case when numero_cuota in (1,2) then flg_cuota_con_atraso else 0 end) flg_atraso_pagado_2_1eras_cuotas,
max(case when numero_cuota in (1,2) then flg_cuota_con_atraso_5_o_mas else 0 end) flg_atraso_5_pagado_2_1eras_cuotas,
max(case when numero_cuota in (1,2) then flg_cuota_con_atraso_15_o_mas else 0 end) flg_atraso_15_pagado_2_1eras_cuotas,
max(case when numero_cuota in (1,2) then flg_cuota_con_atraso_30_o_mas else 0 end) flg_atraso_30_pagado_2_1eras_cuotas,
max(case when numero_cuota in (1,2,3) then flg_cuota_con_atraso else 0 end) flg_atraso_pagado_3_1eras_cuotas,
max(case when numero_cuota in (1,2,3) then flg_cuota_con_atraso_5_o_mas else 0 end) flg_atraso_5_pagado_3_1eras_cuotas,
max(case when numero_cuota in (1,2,3) then flg_cuota_con_atraso_15_o_mas else 0 end) flg_atraso_15_pagado_3_1eras_cuotas,
max(case when numero_cuota in (1,2,3) then flg_cuota_con_atraso_30_o_mas else 0 end) flg_atraso_30_pagado_3_1eras_cuotas,
max(case when numero_cuota in (1,2,3) then flg_cuota_con_atraso_60_o_mas else 0 end) flg_atraso_60_pagado_3_1eras_cuotas,
min(z.nro_dias_recaudados) min_nro_dias_recaudo,
max(z.nro_dias_recaudados) max_nro_dias_recaudo,
avg(z.nro_dias_recaudados) prom_nro_dias_recaudo,
median(z.nro_dias_recaudados) mediana_nro_dias_recaudo,
max(CASE WHEN estado_cuota IN ('PENDING') THEN z.nro_dias_recaudados ELSE NULL END) max_nro_dias_recaudo_pendiente,
max(CASE WHEN estado_cuota IN ('PAID') THEN z.nro_dias_recaudados ELSE NULL END) max_nro_dias_recaudo_pagado,
min(CASE WHEN estado_cuota IN ('PAID') THEN z.nro_dias_recaudados ELSE NULL END) min_nro_dias_recaudo_pagado,
avg(CASE WHEN estado_cuota IN ('PAID') THEN z.nro_dias_recaudados ELSE NULL END) prom_nro_dias_recaudo_pagado,
median(CASE WHEN estado_cuota IN ('PAID') THEN z.nro_dias_recaudados ELSE NULL END) mediana_nro_dias_recaudo_pagado,
max(flg_pendiente_cuota_ya_cubierta) flg_pendiente_cuota_ya_cubierta,
max(nro_cuotas_con_atraso) nro_cuotas_con_atraso,
max(flg_atraso_2_cuotass_o_mas) flg_atraso_2_cuotass_o_mas,
max(dias_restantes) dias_restantes,
max(flg_tiene_pagos) flg_tiene_pagos,
max(flg_tiene_pago_pendiente) flg_tiene_pago_pendiente,
max(flg_pendiente_recaudacion_cero) flg_pendiente_recaudacion_cero,
max(pendiente_dif_dias_ult_dia_reacudo) pendiente_dif_dias_ult_dia_reacudo
FROM (
SELECT q.prestamo_id,
q.cuota_id,
q.numero_cuota_mto numero_cuota,
max(rojos.nro_cuotas_con_atraso) nro_cuotas_con_atraso,
CASE WHEN max(rojos.nro_cuotas_con_atraso) > 1 THEN 1 else 0 END flg_atraso_2_cuotass_o_mas,
max(q.Estado_Desc) estado_cuota,
max(q.monto_cuota_mto) monto_cuota,
max(q.monto_efectivo_pago_mto) monto_efectivo_pago,
max(q.monto_acum_pagado_mto) monto_acum_pagado,
sum(CASE WHEN ii.Tipo_Desc LIKE 'REC_%' THEN ii.Monto_Pagado_Amt ELSE 0 END) monto_recaudado_from_income,
max(ii.Fecha_Thru_dt) ult_fecha_recaudada,
max(ii.Fecha_Proceso_Dt) ult_fecha_reacudo_procesado,
case when max(q.Estado_Desc) IN ('PENDING') THEN CURRENT_DATE - max(ii.Fecha_Thru_dt) ELSE 0 END pendiente_dif_dias_ult_dia_reacudo,
max(rojos.max_atraso_cuotas_pendientes) dias_expirados,
CASE WHEN max(q.Estado_Desc) IN ('PAID') THEN max(q.dias_expirados_mto) ELSE 0 END dias_expirados_pagado,
CASE WHEN max(q.Estado_Desc) IN ('PENDING') THEN max(q.dias_expirados_mto) ELSE 0 END dias_expirados_pendiente,
case when max(q.dias_expirados_mto) > 0 or max(max_atraso_cuotas_pendientes) > 0 then 1 else 0 end flg_cuota_con_atraso,
case when max(q.dias_expirados_mto) >= 5 or max(max_atraso_cuotas_pendientes) > 0 then 1 else 0 end flg_cuota_con_atraso_5_o_mas,
case when max(q.dias_expirados_mto) >= 15 or max(max_atraso_cuotas_pendientes) > 0 then 1 else 0 end flg_cuota_con_atraso_15_o_mas,
case when max(q.dias_expirados_mto) >= 30 or max(max_atraso_cuotas_pendientes) > 30 then 1 else 0 end flg_cuota_con_atraso_30_o_mas,
case when max(q.dias_expirados_mto) >= 60 or max(max_atraso_cuotas_pendientes) > 60 then 1 else 0 end flg_cuota_con_atraso_60_o_mas,
case when max(q.dias_expirados_mto) >= 90 or max(max_atraso_cuotas_pendientes) > 90 then 1 else 0 end flg_cuota_con_atraso_90_o_mas,
max(q.Fecha_Pago_Dt) Fecha_Pago,
max(q.Fecha_Efectiva_Pago_Dt) Fecha_Efectiva_Pago,
max(ii.Monto_Pagado_Amt) max_monto_recaudado_1_dia,
count(distinct(CASE WHEN ii.Tipo_Desc LIKE 'REC_%' THEN ingreso_id else NULL END)) nro_dias_recaudados,
coalesce(max(CASE WHEN ii.Tipo_Desc LIKE 'REC_%' THEN ii.Monto_Pagado_Amt ELSE 0 END) *100.0 / max(q.monto_cuota_mto), 0.0) max_cobertura_recaudo_1_dia,
CASE WHEN sum(CASE WHEN ii.Tipo_Desc LIKE 'REC_%' THEN ii.Monto_Pagado_Amt ELSE 0 END) >= max(q.monto_cuota_mto) AND max(nro_cuotas_con_atraso) <= 1 THEN 1 else 0 END flg_cuota_ya_cubierta,
CASE WHEN sum(CASE WHEN ii.Tipo_Desc LIKE 'REC_%' THEN ii.Monto_Pagado_Amt ELSE 0 END) >= max(q.monto_cuota_mto) AND max(q.Estado_Desc) IN ('PENDING') AND max(nro_cuotas_con_atraso) <= 1 THEN 1 else 0 END flg_pendiente_cuota_ya_cubierta,
CASE WHEN sum(CASE WHEN ii.Tipo_Desc LIKE 'REC_%' THEN coalesce(ii.Monto_Pagado_Amt, 0) ELSE 0 END) <= 0.0 AND max(q.Estado_Desc) IN ('PENDING') THEN 1 else 0 END flg_pendiente_recaudacion_cero,
CASE WHEN max(q.Estado_Desc) IN ('PENDING') THEN max(q.Fecha_Pago_Dt) - CURRENT_DATE else 0 end dias_restantes,
CASE WHEN max(q.Estado_Desc) IN ('PENDING') THEN 1 else 0 end flg_tiene_pago_pendiente,
CASE WHEN max(q.Estado_Desc) IN ('PAID') THEN 1 else 0 end flg_tiene_pagos
FROM E_DW_VIEWS.V_MST_VPC_CIMA_QUOTA q
LEFT JOIN E_DW_VIEWS.V_MST_VPC_CIMA_INCOME ii
ON q.cuota_id = ii.cuota_id
LEFT JOIN (
SELECT prestamo_id,
count(distinct(Numero_Cuota_Mto)) nro_cuotas_con_atraso,
max(Dias_Expirados_Mto) max_atraso_cuotas_pendientes
FROM E_DW_VIEWS.V_MST_VPC_CIMA_QUOTA
WHERE 1 = 1
AND Dias_Expirados_Mto > 0
AND Estado_desc = 'PENDING'
GROUP BY prestamo_id
) rojos
ON q.prestamo_id = rojos.prestamo_id
LEFT JOIN (
SELECT
prestamo_id,
numero_cuota_mto,
ROW_NUMBER() OVER (PARTITION BY prestamo_id ORDER BY numero_cuota_mto ASC) orden_pendiente
FROM E_DW_VIEWS.V_MST_VPC_CIMA_QUOTA
WHERE Estado_Desc IN ('PENDING')
) x
on q.prestamo_id = x.prestamo_id and q.Numero_Cuota_Mto = x.Numero_Cuota_Mto
WHERE (
q.Estado_Desc IN ('PAID') OR x.orden_pendiente = 1
)
GROUP BY q.prestamo_id, q.cuota_id, q.numero_cuota_mto
) z
WHERE z.prestamo_id = 8760
GROUP BY z.prestamo_id
Revise this Paste