Sales and inventory quantity by item by warehouse query

[Former Member]

Hi,

  I am trying to write a query to display Sales Qty, Sales Amount & Inventory Qty for each Item for each Warehouse. However the query result shows the same Sales Qty, Sales Amount & Inventory Qty for both Warehouses.

Appreciate if anyone can help to fix this. Thanks.

SELECT T1.[ItemCode], MAX(T1.[Dscription]), SUM(T1.[Quantity]) 'SALE QTY', SUM(T1.[LineTotal]) 'SALE AMT', T4.[ItmsGrpNam], MAX (T2.[OnHand]) 'INV QTY', T5.[WhsCode]

FROM [dbo].[OINV]  T0

INNER JOIN [dbo].[INV1]  T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN [dbo].[OITM]  T2 ON T1.ItemCode = T2.ItemCode

INNER JOIN [dbo].[OCRD]  T3 ON T0.CardCode = T3.CardCode

INNER JOIN [dbo].[OITB]  T4 ON T2.ItmsGrpCod = T4.ItmsGrpCod

INNER JOIN [dbo].[OITW]  T5 ON T2.ItemCode = T5.ItemCode

WHERE (T5.[WhsCode] = 'ABC' OR T5.[WhsCode] = 'DEF') and  T0.[DocDate] between '2015-02-01' and '2015-08-01'

GROUP BY T1.[ItemCode],  T4.[ItmsGrpNam],  T5.[WhsCode]

ORDER BY T1.[ItemCode],  T4.[ItmsGrpNam],  T5.[WhsCode]

[former_member212181 · Accepted solution]

Hi,

Please try below query

----Query Starts

;WITH Sales as

(

Select Y.ItemCode,Y.WhsCode, Sum(Y.Quantity)[SALQTY], Sum(Y.LineTotal)[SALAMT]

from OINV X

  Inner JOin INV1 Y on X.DocEntry = Y.DocEntry

Where X.Canceled = 'N' and X.DocDate >= [%0]

  and  X.DocDate <= [%1]

  and Y.WhsCode in ('ABC','DEF')

Group By Y.ItemCode,Y.WhsCode

)

Select A.ItemCode, A.ItemName, D.SALQTY, D.SALAMT, C.ItmsGrpNam, B.Onhand [InStock], B.WhsCode

From OITM A

  Inner Join OITW B on A.ItemCode = B.ItemCode

  Inner Join OITB C on A.ItmsGrpCod = C.ItmsGrpCod

  Left Outer Join Sales D on A.ItemCode = D.ItemCode and B.WhsCode = D.WhsCode

----Query Ends

Thanks

Unnikrishnan

[Former Member · Accepted solution]

Hi Unnikrishnan,

    I have tested the query & it is able to pull out the correct answer eg sales qty, amount & inventory for each item & warehouse. However it also generates results for ALL warehouses even without inventory. Hence the output is huge. After adding additional condition B.OnHand>0, it is more manageable and certainly helped a lot. Thanks for your help.

[BGRATTON147]

Hi

Is it easy too add credit note data.

Thanks

BG

[KennedyT21]

Try This

SELECT T1.[ItemCode], MAX(T1.[Dscription]), SUM(T1.[Quantity]) 'SALE QTY', SUM(T1.[LineTotal]) 'SALE AMT',

(select (c.[OnHand]) from oitw c where c.itemcode=t1.itemcode and c.whscode=t1.whscode   ) 'INV QTY',  t1.whscode

FROM [dbo].[OINV]  T0

INNER JOIN [dbo].[INV1]  T1 ON T0.DocEntry = T1.DocEntry

WHERE (T1.[WhsCode] = 'ABC' OR T1.[WhsCode] = 'DEF') and  T0.[DocDate] between '2015-02-01' and '2015-08-01'

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

Regards

Kennedy