Most Recent Cost of an Item Query returning Duplicates

[raziel3]

I'm trying to get the last/most recent cost of an item using this query.

SELECT Product.UPC, Product.PNAME, PurDetail.PurDate, PurDetail.CPU
FROM Product LEFT JOIN PurDetail ON Product.UPC = PurDetail.UPC
WHERE (((PurDetail.PurDate)=(SELECT MAX(PurDate)
FROM PurDetail As P2
WHERE P2.UPC = PurDetail.UPC
AND P2.PurDate <= Date())))
ORDER BY PurDetail.PurDate DESC;

I am getting duplicates in this query which I have managed to narrow down to the fact that returns are being recorded in the PurDetail like this

+--------------+-----------+----------+--------+-----------+----------------------+------+
|     UPC      |  PurDate  | Quantity |  Cost  | ZeroRated |      ENTRYDATE       | PID  |
+--------------+-----------+----------+--------+-----------+----------------------+------+
| 040032125043 | 3/31/2022 |       -3 | -10.67 | No        | 5/12/2022 1:59:43 PM | 4036 |  'returned item
| 040032125043 | 3/31/2022 |       12 |  42.67 | No        | 5/12/2022 2:00:39 PM | 4036 |
+--------------+-----------+----------+--------+-----------+----------------------+------+

I tried adding another criteria

And P2.Quantity >1

but still getting the duplicates.

 

[plog]

Your ordering method is insufficient for your data. You've ordered by PurDate, but it is not unique enough for your needs. You need a better ordering method. Looking at your example above, how would you determine which of those 2 records is the latest? What criteria are you using in your mind to determine that?

 

[jdraw]

Seems to get most recent cost of an item you would need to divide cost by quantity ( TotCost/quantity = cost per unit)

-10.67 /-3 =3.5566
42.67/12 =3.55583
and as plog said PurDate is not unique enough for your needs.

Wouldn't you normally store unit cost, then Qty * unit Cost = totalCost?
What does entrydate represent?

 

[raziel3]

Wouldn't you normally store unit cost, then Qty * unit Cost = totalCost?

Normally yes but in this instance the data entry is much easier entering the quantity and the total cost for that amount. It is because of that I want to get the last cost of the item.

What does entrydate represent?

That is the date the record is entered into the database.

 

[sonic8]

I tried adding another criteria

And P2.Quantity >1

but still getting the duplicates.

Because you apply your criterion to the wrong table.

AND PurDetail.Quantity >=1

In fact, you should apply this criterion to both, the subquery and the main query.

 

[arnelgp]

You may also try:

SELECT Product.UPC, Product.PName, T.MaxOfPurDate AS PurDate, T.CPU
FROM Product LEFT JOIN
(SELECT PurDetail.UPC, Max(PurDetail.PurDate) AS MaxOfPurDate, PurDetail.Quantity, PurDetail.Cost, PurDetail.CPU
FROM PurDetail
GROUP BY PurDetail.UPC, PurDetail.Quantity, PurDetail.Cost, PurDetail.CPU
HAVING (((PurDetail.Cost)>0))
)  AS T ON PRODUCT.UPC = T.UPC;

 

[raziel3]

You may also try:

SELECT Product.UPC, Product.PName, T.MaxOfPurDate AS PurDate, T.CPU
FROM Product LEFT JOIN
(SELECT PurDetail.UPC, Max(PurDetail.PurDate) AS MaxOfPurDate, PurDetail.Quantity, PurDetail.Cost, PurDetail.CPU
FROM PurDetail
GROUP BY PurDetail.UPC, PurDetail.Quantity, PurDetail.Cost, PurDetail.CPU
HAVING (((PurDetail.Cost)>0))
)  AS T ON PRODUCT.UPC = T.UPC;

Still getting Duplicates @arnelgp

@sonic8 I think your method is working.

 

[arnelgp]

i have wrong criteria, it should filter on Quantity:

SELECT Product.UPC, Product.PName, T.MaxOfPurDate AS PurDate, T.CPU
FROM Product LEFT JOIN
(SELECT PurDetail.UPC, Max(PurDetail.PurDate) AS MaxOfPurDate, PurDetail.Quantity, PurDetail.Cost, PurDetail.CPU FROM PurDetail GROUP BY PurDetail.UPC, PurDetail.Quantity, PurDetail.Cost, PurDetail.CPU
HAVING (((PurDetail.Quantity)>0)))  AS T ON PRODUCT.UPC = T.UPC;

query result: