How to only get the newest Invoices for each customer

Classic Servoy
1

Hi,

I have an Invoices table with the following columns:

invoice_id
customer_id
invoice_date
the rest of the columns here

And I want to report only the last Invoice for each customer. How can I filter or process to get only the newest document for each customer?

Thanks

2

Hi:

Try this code:

SELECT cus.customer_id, inv.invoice_id
FROM customers AS cus LEFT JOIN invoices AS inv ON cus.customer_id = inv.customer_id
AND inv.invoice_id = (SELECT max(invs.invoice_id) FROM invoices AS invs WHERE invs.customer_id
= cus.customer_id);

Best regards.

3

Thank you Adelo!

And finaly… what should I add to the SELECT to select only those customers that their last Invoice is > 1000?

4

It’s solved! I added a WHERE inv.ammount > 1000 at the end and works great.

Thanks again

5

The only thing you might want to watch for is that will always give you the newest document but it might not give you the latest invoice (i.e. by date) if that is what you want. For example a wrong invoice could be issued and then deleted when it is discovered; a new one is issued in its place and presumably given the date of the original invoice. In the meantime the customer has had other invoices issued. The ‘newest’ invoice will be the reissued one but it will not be the ‘latest’ in terms of date… That might be OK as you are always after the ‘newest’, most recently ‘created’ invoice from a database perspective.

6

Be aware that a subselect can be costly.
A simple ORDER BY with a LIMIT (or TOP) might be faster on larger tables like so:

SELECT 
    cus.customer_id, 
    inv.invoice_id
FROM 
    customers AS cus 
    LEFT JOIN invoices AS inv ON (cus.customer_id = inv.customer_id)
WHERE 
    cus.customer_id = ?
ORDER BY 
    inv.invoice_id DESC 
LIMIT 1

This syntax works for most database vendors except MSSQL and Sybase. For that you need to use the following syntax:

SELECT TOP 1 
    cus.customer_id, 
    inv.invoice_id
FROM 
    customers AS cus 
    LEFT JOIN invoices AS inv ON (cus.customer_id = inv.customer_id)
WHERE 
    cus.customer_id = ?
ORDER BY 
    inv.invoice_id DESC

If you want the latest invoice date you can order on that column instead.

Hope this helps.

7

Thanks to you both John and Robert!

8

Here is an alternative to get the latest invoice for each customer that should work on all ANSI SQL compatible databases:

SELECT 
    cus.customer_id, 
    max(inv.invoice_id) as invoice_id
FROM 
    customers AS cus 
LEFT JOIN 
    invoices AS inv ON (cus.customer_id = inv.customer_id)
GROUP BY 
    cus.customer_id 
ORDER BY 
    cus.customer_id

If you want to narrow it down to a specific customer and invoices above $1.000 then insert the following where clause:

WHERE
    cus.customer_id = ? and inv.amount > 1000
9

:)

This now becomes more a discussion on portability vs optimization.
The first and last query examples are both portable but costly, the one I posted is fast but not portable to ALL database vendors without a small rewrite or extra code (LIMIT vs TOP).
So there is always a tradeoff on what you choose. Which you should use all depends on your requirements and use case.

10

Hi Robert,

You already know I can be stuborn :D but the question was to get the latest invoice for each customer and that’s exactly what it does. You would have to run your query for every customer and that will definitely take much longer.

Omar

11

omar:
You would have to run your query for every customer…

I thought that was exactly what he wanted. But reading back I see it can be interpreted either way.

12

Hi,

I have a problem using Find and Search on the form with the getDataSetByQuery.

I have three buttons in the form: All/Last, Find and Search. All/Last applies the getDataSetByQuery and loads all records or only the last invoice for each customer, and Find and Search let the user search for specific records in the form.

The problem is when the user searches, when press Search without no search criteria, it returns all records, not the last invoices.

Is there a way to generateDataSetByQuery and then Search and show all the records in the query, not all records?

Regards