mardi 1 décembre 2020

MySQL Select and IF() Statement

I'm kind of new to MySQL and I need help. I have a table Invoices and a table Payments. I am having trouble generating a report that will show all invoices that were paid in In Full or a Partial Payment was received by 12/31/2019. One Invoice can be paid by one or more payments (For example a partial payment, such as 25% down, and the remainder payment on completion of work). How can I create SQL query that will select ALL records from Invoices and then for each Invoice select matching Payment, compare it to the Invoice Total and will display either Paid in Full or partial Payment? I have the following code:

SELECT Invoices.InvoiceID, Invoices.ClientName, Invoices.InvoiceTotal
INNER JOIN InvoiceStatus ON InvoiceStatus.InvoiceStatusID = Invoices.InvoiceStatus
WHERE InvoiceDate BETWEEN '2019-1-1  00:00:00' AND '2019-12-31 23:59:59'
AND (Invoices.InvoiceStatus = '1' OR Invoices.InvoiceStatus = '2')
 AND (Invoices.InvoiceActive != 0 OR Invoices.InvoiceActive IS NULL)
ORDER BY ClientName

SELECT Payment.PaymentID, Payment.PaymentReceivedAmount, Payment.PaymentReceivedDate FROM `Payment` 
INNER JOIN Invoices ON Invoices.InvoiceID = Payment.InvoiceID
WHERE PaymentReceivedDate BETWEEN '2019-1-1  00:00:00' AND '2019-12-31 23:59:59'

If I do INNER JOIN then I get two rows for an Invoice that was paid with two payment. I know that I also need to implement IF() statement to show Paid in Full and Partial Payment but I'm a little lost. Any help would be greatly appreciated!

Aucun commentaire:

Enregistrer un commentaire