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_idAdd a code snippet to your website: www.paste.org