How can you select the most recent invoice per customer via SQL ?
Here is the scenario: You want to display following fields in a report : CUSTID, CONAME, DTEINV, TOTBIL.
You also only want to show, for each customer, the invoice with the highest date (=the most recent invoice). Each customer can have multiple invoices in the database. You could use "Group By" customer and use MAX(DTEINV) but then you cannot add other fields like TOTBIL and so on.
Use the SQL statement below, making use of a subselect to compare the invoice date with the highest invoice date for each customer :
SELECT CUSTID; CONAME; DATE( DTEINV; YYMMDD ); TOTBIL FROM SCDB1.INVHDR INVHDR; SCDB1.CSTHDR CSTHDR WHERE INVHDR.CUSTID = CSTHDR.CUSTID AND ( DIGITS( INVHDR.CUSTID ) CONCAT DIGITS( DTEINV ) IN( SELECT DIGITS( INVHDR.CUSTID ) CONCAT DIGITS( MAX( DTEINV ) ) FROM SCDB1.INVHDR INVHDR GROUP BY INVHDR.CUSTID)) ORDER BY 1
Another example with date field :
SELECT CUSTOMERS.CUST_ID, CUSTNAME, DATEINV, EXTPRICE FROM SCSAMPLE70.CUSTOMERS CUSTOMERS, SCSAMPLE70.ORDERS A WHERE A.CUST_ID = CUSTOMERS.CUST_ID AND ( DATEINV in( SELECT max( B.DATEINV ) FROM SCSAMPLE70.ORDERS B WHERE A.CUST_ID = B.CUST_ID)) ORDER BY 1
Here's an example if using just one file:
SELECT ORDNUM, CUST_ID, MAX( DATEINV ) AS "MaxDateinv", UNIT_COST FROM SCSAMPLE70.ORDERS A WHERE DATEINV in( SELECT max( B.DATEINV ) FROM SCSAMPLE70.ORDERS B WHERE A.CUST_ID = B.CUST_ID ) GROUP BY ORDNUM, CUST_ID, UNIT_COST ORDER BY 1
Ref#: 1475634
Still have questions? We can help. Submit a case to Technical Support.