SAP querie last sold item doesn't show all items

[jeroenw]

Hi,

Can someone please help me with this querie.

I want this querie to display ALL the items. Also the items that are not sold. Now it displays only items that are sold with an order or invoice. I thought LEFT JOIN would do the trick but it doesn't.

SELECT 
T0.[ItemCode], 
T0.[ItemName], 
T1.[WhsCode], 
MAX(T1.[ShipDate]) as 'Last sold on order',
MAX(T1.[ActDelDate]) as 'Last sold on invoice'


FROM 
OITM T0  
LEFT JOIN 
INV1 T1 
ON T0.[ItemCode] = T1.[ItemCode] 


WHERE
T1.[WhsCode] =[%0]


GROUP BY
T0.[ItemCode], 
T0.[ItemName], 
T1.[WhsCode]

[Andre_S]

I suppose you want to filter the items by warehouse where they have on stock quantity, right?
You can get this information from oitw table, joind by ItemCode.
Your join with OWHS based on WhsCode limits the query again to the lines in Inv1.

[jeroenw]

Thanks for your reply.

There is unfortunatly no option to take warehouse information from oitm.

I tried this without succes (gives the same results):

SELECT 
T0.[ItemCode], 
T0.[ItemName], 
T2.[WhsCode], 
MAX(T1.[ShipDate]) as 'Laast verkocht order',
MAX(T1.[ActDelDate]) as 'Laatst verkocht factuur'


FROM 
OITM T0  
INNER JOIN 
INV1 T1 
ON T0.[ItemCode] = T1.[ItemCode] 
INNER JOIN
OWHS T2
ON T1.[WhsCode] = T2.[WhsCode]

WHERE
T2.[WhsCode] =[%0]


GROUP BY
T0.[ItemCode], 
T0.[ItemName], 
T2.[WhsCode]

[Andre_S]

Hi Jeroen,

problem is your where condition. You should take warehouse information from oitm instead of inv1. Your query shows now only items which have warehouse information in inv1 table -> means which were used in Invoice documents.