Search code examples
sqlsql-servermax

Why does it bring repeat items from other purchases when I use max(last price)?


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

Solution

  • 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!