SAP B1 Query Sell Items Quantity

[former_member599250]

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]

[Abdul · Accepted solution]

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]

[former_member599250 · Accepted solution]

Thank you but it doesn't work same problem for one item comes 6-7 results.

[former_member599250 · Accepted solution]

(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.

[former_member599250]

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

[Abdul]

USe group by in your query to repmove duplicates

[Abdul]

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

[former_member599250]

I tried but it comes like so picture.

[former_member280812]

Hi,

What's wrong with this result?

[former_member599250]

I can't see other values and wrong results.

[former_member599250]

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

[azizelmir]

Did you try the query ? it should give you what you mentioned above.

[azizelmir]

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

[Abdul]

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

[former_member599250]

Thank you for your answer. Can you write maybe for this query?