Grouping on Max date for a Customer Record (multiple tables)

Question

Morning all!

We have a table showing customers, their warehouses and when their record was last changed. My task is to find the most recent customer number for a specific warehouse. Most of these haven't changed but there are quite a few that have. I have created the below query that works perfectly to find the last amend date for all warehouses in my table.

Select
  WHS,
  Max(AMENDT) As MAX_AMENDT
From
  MBC6REVQ
Group By
  WHS
Order By
  WHS,
  Max(Distinct (Select
    Max(MAX_AMENDT)
  From
    MBC6REVQ
  Group By
    MBC6REVQ.WHS
  Fetch First
    1 Rows Only)) Desc

The issue I have is I now am required to add the customer number and join to my customer table to retrieve the customer name. Just adding the CUSNO field results in an error so I have to add CUSNO to the 'group by'. This gives me multiple records for each warehouse. My data looks like this:

    WHS   AMENDT
    A01   1150101
    A01   1130704
    A02   1141030
    A07   1071101
    A10   1020905

..so the query correctly returns:

    WHS   AMENDT
    A01   1150101
    A02   1141030
    A07   1071101
    A10   1020905

I need to add the CUSNO column because the customer number changed. My query then looks like:

Select
  WHS,
  CUSNO,
  Max(AMENDT) As MAX_AMENDT
From
  MBC6REVQ
Group By
  WHS, CUSNO
Order By
  WHS,
  Max(Distinct (Select
    Max(AMENDT)
  From
    MBC6REVQ
  Group By
    MBC6REVQ.WHS
  Fetch First
    1 Rows Only)) Desc

...and returns:

    WHS   CUSNO   AMENDT
    A01   1003    1150101
    A01   1056    1130704
    A02   1011    1141030
    A07   1169    1071101
    A10   1012    1020905

...when I need to have:

    WHS   CUSNO   AMENDT
    A01   1003    1150101
    A02   1011    1141030
    A07   1169    1071101
    A10   1012    1020905

Can anyone assist? This is driving me crazy!!! Any help is greatly appreciated!

Thanks in advance

Matt

Answer 1

I don't know all of the capabilities of db2400, so there may be easier/better ways to handle this if it supports windowed functions for example. That said, this is the most "generic" SQL that I can think of, so it's the most likely to work.

SELECT
    T1.WHS,
    T1.CUSNO,
    T1.AMENDT
FROM
    MBC6REVQ T1
LEFT OUTER JOIN MBC6REVQ T2 ON
    T2.WHS = T1.WHS AND
    T2.AMENDT > T1.AMENDT
WHERE
    T2.WHS IS NULL

Alternatively, if db2400 supports NOT EXISTS and correlated subqueries:

SELECT
    T1.WHS,
    T1.CUSNO,
    T1.AMENDT
FROM
    MBC6REVQ T1
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM
            MBC6REVQ T2
        WHERE
            T2.WHS = T1.WHS AND
            T2.AMENDT > T1.AMENDT
    )

In either case, you'll need to also decide how you want to handle ties and code appropriately for that.