I'm banging my head to understand why my query doesn't work as expected. I have two tables: shopping table and products table. I need to extract the last purchase of the item based on what I have available in stock (for stock valuation).
I tried that, but I understand that it's not bringing only the last purchase because it brings me repeated items.
SELECT T0.ItemCode, T0.Dscription,
MAX(T0.Currency)[Currency],
MAX(T0.Price)[Price], T1.OnHand[in stock],
MAX(T0.U_Processo)[Customs process]
FROM Order_Lines T0 RIGHT
JOIN Items T1 ON T0.ItemCode = T1.ItemCode
WHERE T0.ItemCode NOT LIKE '%%DESP-%%'
AND T0.ItemCode = (SELECT TOP 1 T1.ItemCode WHERE T1.OnHand > 0)
AND T0.U_Processo IS NOT NULL /*I used this information because it
was also repeated when the item does not have
this information and when it has this information.
MAX() does not work.*/
GROUP BY T0.ItemCode, T0.Dscription, T1.OnHand, T0.U_Processo
ORDER BY T0.ItemCode
Could someone tell me where I'm going wrong?
Oh! I even noticed a few times that it's bringing me more than once when it's paid in two different currencies, so I put the MAX() in the currency too, but I don't know if it can. I even did the same thing for customs processes.
PS. I Tryed to use MAX(Date), but don't work too
This is an example:
ItemCode | Dscription | Currency | Price | in Stock | Customs Process |
---|---|---|---|---|---|
I0028015A-001 | ECLISSE TABLE LAMP 110V | EUR | 92.150000 | 5.000000 | PQU 20-045 |
I0028035A-001 | ECLISSE TABLE LAMP 110V | EUR | 92.150000 | 4.000000 | PQU 20-045 |
I just needed to correct the way I was fetching the data. I'm answering here so you can be registered.
SELECT T0.ItemCode, T0.ItemName, T0.OnHand, x.preco, x.moeda, x.Taxa, x.processo
FROM Items T0
LEFT JOIN (
SELECT T1.ItemCode, MAX(T1.Price) as preco, MAX(T1.Currency) as moeda, MAX(T1.Rate) as Taxa,
MAX(T1.U_Process) as processo, MAX(T1.DocDate) as UltData
FROM Purchase_Lines T1
GROUP BY T1.ItemCode
) x ON T0.ItemCode = X.ItemCode
WHERE T0.OnHand > 0
Tks for @JohnCappelletti and @RobertHamilton for help me!