Skip to Content

SAP querie last sold item doesn't show all items

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]
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on Jul 15 at 07:14 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 15 at 07:28 AM

    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]
    
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 15 at 09:31 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.