Select the Most Recenct (Invoice)

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.

Solution

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