Query to return latest record between 2 tables

Question

I'm trying to pull the most current date (cusPurDt) and corresponding amount (subAmt) by customer (cusID/subID). Here is some sample data:

create table customer (
    cusID char(5),
    cusPurDt varchar(10),
    cusCat char(1),
    cusSer varchar(10) );

insert into customer (
    cusID, cusPurDt, cusCat,cusSer)
values ('10606','2/1/2023','3','602AAA7AD7'),
    ('10606','2/1/2023','2','B65783450D'),
    ('10606','7/10/2023','8','0025C19E2E'),
    ('10606','9/1/2023','7','5884F5F5D3'),
    ('10606','11/1/2023','3','47DBB80FE0'),
    ('10606','2/1/2024','2','6A1AC595B0'),
    ('11065','1/23/2023','8','F6D8F095EF'),
    ('11065','5/1/2023','3','0998DB1AE9'),
    ('11065','9/1/2023','3','86C2D33F34'),
    ('11065','10/1/2023','3','CB8C096609'),
    ('11065','12/1/2023','8','A2F02722AB'),
    ('11065','1/1/2024','2','33C2EC428C'),
    ('11543','10/1/2022','3','F72055A3AD'),
    ('11543','4/1/2023','3','8AA893ED4E'),
    ('11543','7/1/2023','3','0AF8266793'),
    ('11543','9/1/2023','2','4833F90ADF'),
    ('11572','12/1/2022','8','429D4661AB'),
    ('11572','2/1/2023','2','619B80AD54'),
    ('11572','3/1/2023','2','5B550DB0E1'),
    ('11572','6/1/2023','3','C691B08B3B'),
    ('11572','7/1/2023','3','929CAB873D'),
    ('11804','5/1/2022','3','F95112C30B'),
    ('11804','6/1/2022','3','D0A55AF485'),
    ('11804','10/1/2022','3','53D1ADFAFC'),
    ('11804','11/1/2022','2','9C84EA5339'),
    ('11804','2/1/2023','3','C5324A5E12'),
    ('11804','5/1/2023','3','B1BA49E143'),
    ('11804','6/1/2023','3','B8DE19A0E6'),
    ('11804','8/1/2023','3','47A6D2DDFE'),
    ('11804','11/1/2023','2','C211A332DE');

create table vendor (
    subID char(5),
    subAmt char(10),
    subSer varchar(10) );

insert into vendor (
    subID, subAmt, subSer)
values ('10606','00100','602AAA7AD7'),
    ('10606','0098','B65783450D'),
    ('10606','00010','0025C19E2E'),
    ('10606','00142','5884F5F5D3'),
    ('10606','00215','47DBB80FE0'),
    ('10606','00057','6A1AC595B0'),
    ('11065','00036','F6D8F095EF'),
    ('11065','00349','0998DB1AE9'),
    ('11065','00234','86C2D33F34'),
    ('11065','00349','CB8C096609'),
    ('11065','00234','A2F02722AB'),
    ('11065','00063','33C2EC428C'),
    ('11543','00122','F72055A3AD'),
    ('11543','00641','8AA893ED4E'),
    ('11543','00106','0AF8266793'),
    ('11543','00099','4833F90ADF'),
    ('11572','00074','429D4661AB'),
    ('11572','00211','619B80AD54'),
    ('11572','00372','5B550DB0E1'),
    ('11572','00068','C691B08B3B'),
    ('11572','00028','929CAB873D'),
    ('11804','00155','F95112C30B'),
    ('11804','00155','D0A55AF485'),
    ('11804','00155','53D1ADFAFC'),
    ('11804','00077','9C84EA5339'),
    ('11804','00002','C5324A5E12'),
    ('11804','00002','B1BA49E143'),
    ('11804','00002','B8DE19A0E6'),
    ('11804','00423','47A6D2DDFE'),
    ('11804','00397','C211A332DE');

The desired result should look like this:

cusID	cusPurDt	subAmt
10606	2/1/2024	00057
11065	1/1/2024	00063
11543	9/1/2023	00099
11572	7/1/2023	00028
11804	11/1/2023	00397

Here is as far as I got with a working query:

select c.cusID
    ,max(c.cusPurDt)
    ,max(v.subAmt)
from customer c
inner join vendor v on v.subID = c.cusID
    and c.cusSer = v.subSer
where c.cusCat in ('1','2','3','4','7','8')
    and c.cusPurDt between curdate() - 365 and curdate()
group by c.cusID

I know using max (or min) on v.subAmt gives the wrong value. I'm assuming there is a way to pull it in a subquery, but am not learned enough to figure that out.

It is a third party application and database so I'm a little limited in what I can do (ex: throws error when using distinct: scalar function not found). They have told me that it is running on SQL server.

Hope this makes sense. Thanks in advance.

Edit: when trying to figure this out in dbfiddle, found that curdate() is a MySQL function, so maybe the powers that be gave me bad info.

Answer 1

You can use with MySQL 8.x Window Functions

And a word of advise save MySQL dates in iso norm format '2019-09-07'

WITH CTE AS (select c.cusID
    ,ROW_NUMBER() OVER(PARTITION BY c.cusID ORDER BY c.cusPurDt DESC) rn
  ,c.cusPurDt
    ,v.subAmt
from customer c
inner join vendor v on v.subID = c.cusID
    and c.cusSer = v.subSer
where c.cusCat in ('1','2','3','4','7','8')
   # and c.cusPurDt between curdate() - 365 and curdate()
)
SELECT
  cusID, cusPurDt,subAmt
FROM CTE WHERE rn = 1

cusID	cusPurDt	subAmt
10606	9/1/2023	00142
11065	9/1/2023	00234
11543	9/1/2023	00099
11572	7/1/2023	00028
11804	8/1/2023	00423

fiddle

For older versions (and much uglier)

SELECT 
    cusID
  , cusPurDt
  ,subAmt
FROM (
SELECT
  c1.cusID
  , c1.cusPurDt
  ,c1.subAmt
    ,IF ( @cust_id = c1.cusID,(@row_number := @row_number + 1),@row_number := 1)  AS rnk
  ,@cust_id := c1.cusID
FROM
(SELECT
  c.cusID
  , c.cusPurDt
  ,v.subAmt
from customer c
inner join vendor v on v.subID = c.cusID
    and c.cusSer = v.subSer
ORDER BY c.cusID ASC, STR_TO_DATE(c.cusPurDt, '%c/%d/%Y') DESC
  LIMIT 18446744073709551615) c1,
(SELECT @row_number := 0, @cust_id := '') AS x
  ) c2
  WHERE rnk = 1;

cusID	cusPurDt	subAmt
11804	11/1/2023	00397
10606	2/1/2024	00057
11572	7/1/2023	00028
11065	1/1/2024	00063
11543	9/1/2023	00099

fiddle

Answer 2

After much digging, I stumbled across a pretty clean solution to my issue (link here).

Here's the query:

select b.cusID
    ,a.maxDt
    ,v.subAmt
from customer b
inner join vendor v on v.subID = b.cusID
    and v.subSer = b.cusSer
inner join (
    select cusID
        ,max(cusPurDt) as maxDt
    from customer
    where cusPurDt between curdate() - 365 and curdate()
        and cusCat in ('1','2','3','4','7','8')
    group by cusID
    ) a
    on a.cusID = b.cusID
        and a.maxDt = cusPurDt
order by b.cusID

It was just a matter of finding the max date and filtering criteria in the subquery, then matching the results in the main query.