How to add unpaid installments with SQL?

Question

I have 3 tables faturas, parcelas and pagamentos. Now I need to mount an SQL to count the installments that are not paid. I have tried a SUM(CASE but it didn’t work out.

Table structure faturas:

id_fa
tipo
data_emissao
valor_fatura

Table structure parcelas:

id_pa
id_fatura
data_vcto
valor_parcela

Table structure pagamentos:

id_pg
id_parcela
data_pgto
valor_bruto
valor_desconto
valor_juros
valor_multa
valor_pago

My attempt to consult:

SELECT 
id_pa, 
SUM(CASE WHEN lc_pagamentos.id_parcela=lc_parcelas.id_pa THEN lc_pagamentos.valor_bruto ELSE 0 END) AS vBruto 
FROM lc_faturas, lc_parcelas, lc_pagamentos 
WHERE lc_faturas.ID=lc_parcelas.ID

This way it only counts the number of installments, but I need you to compare the field valor_parcela table parcelas with the sum of the field valor_bruto table pagamentos and return only the records that are different, ie return me the portion that is not settled.

Answer 1

Greetings!

SELECT par.id_pa as Parcela, par.valor_parcela, valor_pago FROM parcelas AS par LEFT JOIN
(SELECT id_parcela, SUM(valor_bruto) as valor_pago FROM pagamentos GROUP BY id_parcela) as pgtos ON pgtos.id_parcela = par.id_pa 

The above query will show you the Installment Id, the amount of the installment and the amount paid so far (if there is payment for the installment in question). If you only want the installments that have not been paid (partially paid or that have not received any payment) add the following excerpt:

WHERE valor_pago < valor_parcela OR valor_pago IS NULL

I hope I’ve helped.