1

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.

0

2 Answers 2

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

2
  • It seems I'm limited using windows functions. When trying the subquery first in your solution, it fired back with this: Expected lexical element not found: FROM (line 2). I've run into issues in the past with this db using some windows functions. Also tried the second with cte and threw another error: Statement must start with CREATE, DROP, SELECT, INSERT, UPDATE, ALTER, EXECUTE, DELETE, GRANT, REVOKE, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, SET, DEBUG or a script statement. I've been limited in the past with queries that I've had to build with this third party db/application.
    – tkmagnet
    Feb 28 at 19:31
  • I added a mysql 5.7 version, but this is much uglier
    – nbk
    Feb 28 at 22:42
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.