CODEWITHSUNDEEP
sqloracle-database
Alex Anderson
Alex Anderson

Reputation: 153

SQL query to report on what invoices have not been paid, ordered by invoice number

new to SQL and only been doing it for a week and a half. So I apologise now for the question being simple or appearing to be stupid.

I want to present a report on invoices that have not been paid by a given date, ordered by invoice number.

This is how I have displayed, the paid invoices but without the given date.. How do I display, the invoices that have not been paid by say 31-MAR-14. 

SELECT INVOICE.INVOICE_NUMBER, INVOICE.INVOICE_DATE, PAYMENT.PAYMENT_NO, PAYMENT.INVOICE_NUMBER
FROM INVOICE, PAYMENT
WHERE INVOICE.INVOICE_NUMBER = PAYMENT.INVOICE_NUMBER
ORDER BY INVOICE.INVOICE_NUMBER;

Upvotes: 0

Views: 6387

Answers (2)

steinmas
steinmas

Reputation: 398

You need to join the two tables together with a LEFT JOIN, then look to see where the payment date is > 31-MAR-14. Try something like this:

SELECT INVOICE.INVOICE_NUMBER, INVOICE.INVOICE_DATE, PAYMENT.PAYMENT_NO, PAYMENT.INVOICE_NUMBER
FROM INVOICE
LEFT JOIN PAYMENT ON INVOICE.INVOICE_NUMBER = PAYMENT.INVOICE_NUMBER
WHERE PAYMENT.Date_of_payment > '3/31/2014'
ORDER BY INVOICE.INVOICE_NUMBER;

This looks for all payments that were made after 3/31/2014. You may need to add another condition, that limits what invoices you're looking for.

To check payments that have not been made, look where any field in the PAYMENT table is null. SQL like this:

SELECT INVOICE.INVOICE_NUMBER, INVOICE.INVOICE_DATE, PAYMENT.PAYMENT_NO, PAYMENT.INVOICE_NUMBER
FROM INVOICE
LEFT JOIN PAYMENT ON INVOICE.INVOICE_NUMBER = PAYMENT.INVOICE_NUMBER
WHERE INVOICE.INVOICE_DATE = '3/31/2014'
AND PAYMENT.PAYMENT_NO IS NULL
ORDER BY INVOICE.INVOICE_NUMBER;

LEFT JOIN in this case will always return values for the INVOICE table, but may return NULL values in place of the PAYMENT if a payment has not been entered yet. Checking AND PAYMENT.PAYMENT_NO IS NULL will tell you that a payment has not been made yet.

Upvotes: 0

user3375577
user3375577

Reputation: 

    SELECT PAYMENT.PAYMENT_NO
    FROM INVOICE, PAYMENT
    WHERE INVOICE.INVOICE_NUMBER = PAYMENT.INVOICE_NUMBER
    AND INVOICE.INVOICE_DATE = 'AAAAMMJJ'
    ORDER BY INVOICE.INVOICE_NUMBER;

Try something like this. I have question. How do you say the invoice are not paid ?

Upvotes: 1

Related Questions