How to add unmatched parcels with SQL?

Question

How to add unmatched parcels with SQL?

Navigation

#1 by (2 votes)

4

I have 3 tables faturas , parcelas and pagamentos . Now I need to mount an SQL to count the parcels that are not removed. I already tried SUM(CASE but it did not work.

Structure of the faturas table:

id_fa
tipo
data_emissao
valor_fatura

Structure of the parcelas table:

id_pa
id_fatura
data_vcto
valor_parcela

Structure of the pagamentos table:

id_pg
id_parcela
data_pgto
valor_bruto
valor_desconto
valor_juros
valor_multa
valor_pago

My query attempt:

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

In this way it only counts the number of parcels, but I need to compare the valor_parcela field of the parcelas table with the sum of the valor_bruto field of the pagamentos table and return only the records that are different, that is, return the portion that is not removed.

php sql count

asked by anonymous 24.01.2016 / 13:56

1 answer

problems with accents when running php Show post with ajax

score 2 · Accepted Answer

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 Parcel Id, the parcel value and the amount paid so far (if there is payment for the parcel in question). If you want only those portions that have not been removed (partially paid or not received), add the following excerpt:

WHERE valor_pago < valor_parcela OR valor_pago IS NULL

I hope I have helped.