Purchase Price base on Latest Trans Date of each item from a same Item Purchase Table

Question

I have a query which I want to list all latest transaction date for the purchase price for each stock item.

I used aggregate MAX() to list all latest transaction for each item without including the price and it works perfect. But when I included the price, the results was having multiple dates with different prices for each item.

So I try to use sub query but it resulted in error.

Below is my query using a sample item as a test

select
  a.a7itno as "Item No", a.a7appr as "Unit Price", 
  b.maxtrdate as "Trans Date"
from m3edbprod.fcaavp a
Left Join
( select a7itno,max(a7trdt)as "maxtrdate"
from m3edbprod.fcaavp
group by a7itno) b
on a.a7itno=b.a7itno and a.a7trdt=b.maxtrdate
where a.a7itno='110ABC452'

The error appears when run:

Error: SQL0205 - Column MAXTRDATE not in table B in *N. (State:S0022, Native Code: FFFFFF33)

The Expected results should output each item with a single price and latest transdate such as tabulated below :

Item No  Unit Price Trans Date
-----------------------------
110ABC452    100.00      20210920

Note: the date is in YYYYMMDD, if I can set it to date format like 20/09/2021 or 20-09-2021 will also be good.

Will appreciate if I could get some advise from here.

What database are you using? – The Impaler Nov 25 '21 at 01:41 — The Impaler, Nov 25 '21 at 01:41

RoMEoMusTDiE · Answer 1 · 2021-11-25T02:42:44.213

0

Using MAX as a subquery to get the most recent date

SELECT  
  a.a7itno AS "Item No",
  a.a7appr AS "Unit Price",
  a.a7trdt AS "Trans Date"
FROM m3edbprod.fcaavp a
WHERE a7trdt = (SELECT
                 MAX(b.a7trdt)
                 FROM m3edbprod.fcaavp b
                 WHERE b.a7itno = a.a7itno
                )
AND a.a7itno = '110ABC452'

edited Nov 25 '21 at 02:42

answered Nov 25 '21 at 02:01

RoMEoMusTDiE

4,739
1
17
26

Thanks Rome for your coding. When running it got this error: Error: SQL0205 - Column MAXTRDATE not in table FCAAVP in M3EDBPROD. (State:S0022, Native Code: FFFFFF33) – Vincent Lee Nov 25 '21 at 02:04
sorry, can you please try again – RoMEoMusTDiE Nov 25 '21 at 02:08
I also try to correct the query of yours on the a.maxtrdate to b.maxtrdate , it resulted in another error : Error: SQL5001 - Column qualifier or table B undefined. (State:S1000, Native Code: FFFFEC77) – Vincent Lee Nov 25 '21 at 02:14
@VincentLee check the updated answer... that column a.maxtrdate should not be there. – RoMEoMusTDiE Nov 25 '21 at 02:35
Rome, When I run this statement I got the correct date under the column name maxtrdate : SELECT --- get maxdate of the item MAX(a7trdt) AS "maxtrdate" FROM m3edbprod.fcaavp – Vincent Lee Nov 25 '21 at 02:40
@VincentLee try again the updated answer – RoMEoMusTDiE Nov 25 '21 at 02:42

score 0 · Answer 2 · edited Nov 26 '21 at 20:26

0

Finally, I get the desired result thanks to this query :

SELECT a.A7ITNO, a.A7APPR, a.A7TRDT
FROM m3edbprod.FCAAVP a
INNER JOIN (select A7ITNO, max(A7TRDT*100000+A7RGTM) maxdatetime FROM m3edbprod.FCAAVP group by A7ITNO) b
 ON a.A7ITNO = b.A7ITNO and a.A7TRDT*100000+a.A7RGTM = b.maxdatetime
AND a.a7itno='110ABC452'

edited Nov 26 '21 at 20:26

Elikill58

4,050
24
23
45

answered Nov 25 '21 at 06:50

Vincent Lee

1
1

which is basically the same as my answer – RoMEoMusTDiE Nov 30 '21 at 23:51

Purchase Price base on Latest Trans Date of each item from a same Item Purchase Table

2 Answers2