Query To Display Qty Purchased & Used

[RahF]

Hello Experts

I need a query that asks a user to input a range of stock codes and a date range

The query results should display the Stock Code, Quantity purchased and Quantity used in a production order

I don't know which tables I need to use to even try

Would someone please help me

Thanks and Regards

Rahul

[former_member212181 · Accepted solution]

Hi Rahul,

Please try below query and give me feedback

Select A.DocDate

  ,  Case

  When A.TransType = -2 then 'OB'

  When A.TransType = 13 then 'AR Invoice'

  When A.TransType = 14 then 'AR Credit Memo'

  When A.TransType = 15 then 'Delivery'

  When A.TransType = 16 then 'Delivery Return'

  When A.TransType = 18 then 'Ap Invoice'

  When A.TransType = 19 then 'Ap Credit Memo'

  When A.TransType = 20 then 'GRPO'

  When A.TransType = 21 then 'Goods Return'

  When A.TransType = 59 and ApplObj = 202 then 'Receipt Prdn.'

  When A.TransType = 67 then 'Inventory Transfer'

  When A.TransType = 202 then 'Production Order Closing'

  Else 'Others'

  end 'Type'

, A.BASE_REF, A.ItemCode, B.ItemName, A.InQty , A.OutQty, A.TransValue, A.Warehouse, C.ItmsGrpNam

From dbo.OINM A

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

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

Where A.DocDate >= [%0]

  and A.DocDate <= [%1]

  and (A.ItemCode Like '%[%2]%' or A.ItemCode Like '%[%3]%')

  and C.ItmsGrpNam Like '%[%4]%'

Thanks

Unnikrishnan

[RahF · Accepted solution]

Hi Unnikrishnan

I was working on the query as well and have written the below

SELECT T1.[ItemCode] as 'Stock Code',

T1.[ItemName] as 'Stock Description',

T0.[InQty] as 'Total Purchased',

T0.[OutQty] as 'Total Used'

FROM [dbo].[OINM]  T0 INNER JOIN [dbo].[OITM]  T1 ON T0.ItemCode = T1.ItemCode

WHERE

T1.[ItemCode] >=[%0] and 

T1.[ItemCode] <=[%1] and 

T0.[DocDate] >=[%2] and 

T0.[DocDate]  <=[%3]

GROUP BY T1.[ItemCode], T1.[ItemName], T0.[InQty], T0.[OutQty]

ORDER BY T1.[ItemCode]

What this and the query you have given me does it, displays the results line by line

I need the query to sum the lines and display only one line with total purchased and total used

The user can enter a range of stock codes to check usage

Hence I need one line results

Thanks and Regards

Rahul

[former_member212181 · Accepted solution]

Hi Rahul,

Then please use this query

SELECT T1.[ItemCode] as 'Stock Code',

T1.[ItemName] as 'Stock Description',

Sum(T0.InQty) as 'Total Purchased',

Sum(T0.OutQty) as 'Total Used'

FROM [dbo].[OINM]

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

WHERE

T1.[ItemCode] >=[%0] and

T1.[ItemCode] <=[%1] and

T0.[DocDate] >=[%2] and

T0.[DocDate]  <=[%3]

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

ORDER BY T1.[ItemCode]

Thanks

Unnikrishnan

[RahF · Accepted solution]

Hi Unnikrishnan

Works right

Thanks for the help

Rahul

[frank_wang6]

Data can be found in OINM view.

It has all inventory related transaction.