Skip to Content

SAP B1 Query Sell Items Quantity

I want to add in this Query my sell items Quantity for last month. Items come 2 times-3 times. I want to add SUM(t4.quantity) but it doesn't work. Can you help me?

Thank you

SELECT DISTINCT 
T0.ItemCode, 
T0.ItemName,
T1.OnHand AS 'Menge Lagerplatz 01',
T1.[OnHand] - T1.[IsCommited] + T1.[OnOrder] AS 'Verfügbar Lager 01',
T2.OnHand AS 'Menge Lagerplatz 02',
T2.[OnHand] - T2.[IsCommited] + T2.[OnOrder] AS 'Verfügbar Lager 02',
T4.[Quantity],
T3.ItmsGrpNam
FROM OITM T0  

INNER JOIN OITB T3
ON T0.ItmsGrpCod = T3.ItmsGrpCod

INNER JOIN INV1 T4 
ON T0.[ItemCode] = T4.[ItemCode]

INNER JOIN OITW T1 
ON T0.ItemCode = T1.ItemCode 
AND T1.WhsCode = '01'

INNER JOIN OITW T2 
ON T0.ItemCode = T2.ItemCode 
AND T2.WhsCode = '02'

WHERE 
T0.frozenFor = 'N'

AND (T1.[OnHand] - T1.[IsCommited] + T1.[OnOrder] >= 2 AND T1.WhsCode = '01')
AND (T2.[OnHand] - T2.[IsCommited] + T2.[OnOrder] = 0 AND T2.WhsCode = '02')
AND (T1.[OnHand] - T1.[IsCommited] + T1.[OnOrder]) > (T2.[OnHand] - T2.[IsCommited] + T2.[OnOrder])

ORDER BY
T0.ItemCode,
T4.[Quantity]
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

7 Answers

  • Best Answer
    Posted on Jun 24, 2019 at 08:42 PM

    Here is example

    SELECTDISTINCT

    T0.ItemCode, 
    T0.ItemName,
    T1.OnHand AS'Menge Lagerplatz 01',
    T1.[OnHand]- T1.[IsCommited]+ T1.[OnOrder]AS'Verfügbar Lager 01',
    T2.OnHand AS'Menge Lagerplatz 02',
    T2.[OnHand]- T2.[IsCommited]+ T2.[OnOrder]AS'Verfügbar Lager 02',
    T4.[Quantity],
    T3.ItmsGrpNam,
    
    (Select Sum(INV1.Quantity) From INV1 Inner Join OINV b on INV1.DocEntry = b.DocEntry where INV1.ItemCode = T4.ItemCode and Month(b.DocDate) =  Month(OINV.DocDate)-1) as "Previous Month"
    
    
    FROM OITM T0  
    
    INNERJOIN OITB T3
    ON T0.ItmsGrpCod = T3.ItmsGrpCod
    
    INNERJOIN INV1 T4 
    ON T0.[ItemCode]= T4.[ItemCode]INNERJOIN OITW T1 
    ON T0.ItemCode = T1.ItemCode 
    AND T1.WhsCode ='01'
    inner join OINV on OINV.DocEntry = T4.DocEntry
    INNERJOIN OITW T2 
    ON T0.ItemCode = T2.ItemCode 
    AND T2.WhsCode ='02'WHERE 
    T0.frozenFor ='N'AND(T1.[OnHand]- T1.[IsCommited]+ T1.[OnOrder]>=2AND T1.WhsCode ='01')AND(T2.[OnHand]- T2.[IsCommited]+ T2.[OnOrder]=0AND T2.WhsCode ='02')AND(T1.[OnHand]- T1.[IsCommited]+ T1.[OnOrder])>(T2.[OnHand]- T2.[IsCommited]+ T2.[OnOrder])ORDERBY
    T0.ItemCode,
    T4.[Quantity]
    Add a comment
    10|10000 characters needed characters exceeded

    • (SelectSum(INV1.Quantity)From INV1 InnerJoin OINV b on INV1.DocEntry = b.DocEntry where INV1.ItemCode = T4.ItemCode and Month(b.DocDate)= Month(OINV.DocDate)-1)as"Previous Month"

      I changed it so and now it works

      (Select Sum(INV1.Quantity) From INV1 Inner Join OINV b on INV1.DocEntry = b.DocEntry where INV1.ItemCode = T4.ItemCode and b.DocDate >= [%0] and b.DocDate <= [%1])

      Thank you for all answer.

  • Posted on Jun 24, 2019 at 08:14 PM

    you can use subquery with month-1 function in where clause of subquery

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 25, 2019 at 08:43 AM

    Hi Onurcan,

    Could you try this modification:

    SELECT 
    	W1.ItemCode,
    	W1.ItemName,
    	Sum(W1.[Quantity]) as 'Total Qty'
    From
    (SELECT DISTINCT 
    T0.ItemCode, 
    T0.ItemName,
    T1.OnHand AS 'Menge Lagerplatz 01',
    T1.[OnHand] - T1.[IsCommited] + T1.[OnOrder] AS 'Verfügbar Lager 01',
    T2.OnHand AS 'Menge Lagerplatz 02',
    T2.[OnHand] - T2.[IsCommited] + T2.[OnOrder] AS 'Verfügbar Lager 02',
    T4.[Quantity],
    T3.ItmsGrpNam
    FROM OITM T0  
    INNER JOIN OITB T3
    ON T0.ItmsGrpCod = T3.ItmsGrpCod
    INNER JOIN INV1 T4 
    ON T0.[ItemCode] = T4.[ItemCode]
    INNER JOIN OITW T1 
    ON T0.ItemCode = T1.ItemCode 
    AND T1.WhsCode = '01'
    INNER JOIN OITW T2 
    ON T0.ItemCode = T2.ItemCode 
    AND T2.WhsCode = '02'
    WHERE 
    T0.frozenFor = 'N'
    AND (T1.[OnHand] - T1.[IsCommited] + T1.[OnOrder] >= 2 AND T1.WhsCode = '01')
    AND (T2.[OnHand] - T2.[IsCommited] + T2.[OnOrder] = 0 AND T2.WhsCode = '02')
    AND (T1.[OnHand] - T1.[IsCommited] + T1.[OnOrder]) > (T2.[OnHand] - T2.[IsCommited] + T2.[OnOrder])
    ) w1
    Group by W1.ItemCode, W1.ItemName
    ORDER BY W1.ItemCode
    
    

    Thank you,

    Aziz

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 25, 2019 at 09:52 AM

    I am sorry. I take your time but i totally want so something


    example1.png (183.6 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 25, 2019 at 02:40 PM

    I tried but it comes like so picture.


    example1.png (152.4 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 26, 2019 at 06:53 AM

    That item could be in multiple invoices, you need to check first on what basis its showing multiple results then you can correct. Duplication is from your main query not from subquery. Becuase subquery never works in case of multiple results from sub query

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 26, 2019 at 08:16 AM

    I don't have multiple invoices. I have screenshoot. I can explain much better with screenshoot. I sold only one item in a month. But in this query different values.

    SELECT DISTINCT
    T0.ItemCode, 
    T0.ItemName,
    T1.OnHand AS 'Menge Lagerplatz 01',
    T1.[OnHand]- T1.[IsCommited]+ T1.[OnOrder] AS 'Verfügbar Lager 01',
    T2.OnHand AS 'Menge Lagerplatz 02',
    T2.[OnHand]- T2.[IsCommited]+ T2.[OnOrder] AS 'Verfügbar Lager 02',
    T3.ItmsGrpNam,
    
    (Select Sum(INV1.Quantity) From INV1 Inner Join OINV b on INV1.DocEntry = b.DocEntry where INV1.ItemCode = T4.ItemCode and Month(b.DocDate) =  Month(OINV.DocDate)-1) as "Previous Month"
    
    FROM OITM T0  
    
    INNER JOIN OITB T3
    ON T0.ItmsGrpCod = T3.ItmsGrpCod
    
    INNER JOIN INV1 T4 
    ON T0.[ItemCode]= T4.[ItemCode] INNER JOIN OITW T1 
    ON T0.ItemCode = T1.ItemCode 
    AND T1.WhsCode ='01'
    inner join OINV on OINV.DocEntry = T4.DocEntry
    INNER JOIN OITW T2 
    ON T0.ItemCode = T2.ItemCode 
    AND T2.WhsCode ='02' 
    WHERE 
    T0.frozenFor ='N' AND (T1.[OnHand]- T1.[IsCommited]+ T1.[OnOrder]>=2 AND T1.WhsCode ='01') AND (T2.[OnHand]- T2.[IsCommited]+ T2.[OnOrder]=0 AND T2.WhsCode ='02') AND (T1.[OnHand]- T1.[IsCommited]+ T1.[OnOrder])>(T2.[OnHand]- T2.[IsCommited]+ T2.[OnOrder]) 
    ORDER BY
    T0.ItemCode,
    T0.ItemName
    


    example2.png (191.3 kB)
    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.