Invoices not paid more than 10 days

Question

I have a table of invoices and customers.

In the invoice table, for example, I have the cliente_id, status and date fields

In client table id, name, etc...

What I need is to list all the invoices, which the status is still in Open (in NP case - unpaid). But I only wish to list invoices that have not been paid for more than 10 days.

Example of the listing

Cliente     Data de Vencimento   Valor
Teste       10/08/2016           R$ 100,00
Teste       09/08/2016           R$ 200,00
Teste       05/08/2016           R$ 100,00
Teste       01/08/2016           R$ 100,00
Teste       10/07/2016           R$ 100,00

I use PHP and Mysql.

Answer 1

You can use the DATEDIFF() to check how many days passed from a certain date making INNER JOIN with the clients table.

SELECT 
    clientes.nome, faturas.data, faturas.valor
FROM
    faturas
        INNER JOIN
    clientes ON (faturas.cliente_id = clientes.id)
WHERE
    faturas.status = 'NP'
        AND DATEDIFF(NOW(), faturas.data) >= 10

Answer 2

Something like that:

SELECT * FROM faturas WHERE status = 'NP' AND vencimento BETWEEN CURRENT_DATE()-10 AND CURRENT_DATE();

As you did not post the structure of the tables, I could not give a query more appropriate to your situation.

Still, I hope it helps!