Number of times an Item of Stock has been Received (Goods Receipt PO)?

[Former Member]

Hi Everyone,

I have been asked to create a report to show how often a stock item has been received amongst other details; such as the last Goods Receipt Purchase Order #, and date!

Here is the query that I have created; it is doing everything except returning the number of times that a given Item Code has been received (which is the most important part!)

SELECT
T0.ItemCode AS 'Item Code'
, T0.Dscription AS 'Description'
, MAX(T1.DocNum) AS 'Last Goods Receipt PO #'
, MAX(T1.DocDate) AS 'Last Receipt Date'
, SUM(T0.Quantity) AS 'Received since Go Live!'
, T2.OnHand AS 'Currently On Hand'
, SUM(T1.DocNum) AS '# of Times Received'    --Number of Goods Receipt Purchase Orders for the given ItemCode

FROM AU.dbo.PDN1 T0
INNER JOIN AU.dbo.OPDN T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN AU.dbo.OITM T2 ON T2.ItemCode = T0.ItemCode

GROUP BY T0.ItemCode, T0.Dscription, T2.OnHand

ORDER BY T0.ItemCode

The line SUM(T1.DocNum) AS '# of Times Received' is actually returning the SUM of all Document Numbers associated with a given Item Code, whereas I really just want to know how many times a given Item has been received! For example if there are 12 Goods Receipt Purchase Orders for Item 'X' then I simply want to show 12, rather than some obscenely big (meaningless) number.

Any help here will be greatly appreciated.

Kind Regards,

David

[Former Member · Accepted solution]

hi,

use below query

	SELECT
	T0.ItemCode AS 'Item Code'
	, T0.Dscription AS 'Description'
	, MAX(T1.DocNum) AS 'Last Goods Receipt PO #'
	, MAX(T1.DocDate) AS 'Last Receipt Date'
	, SUM(T0.Quantity) AS 'Received since Go Live!'
	, T2.OnHand AS 'Currently On Hand'
	, count (distinct T1.DocNum) AS '# of Times Received'
	FROM PDN1 T0
	INNER JOIN OPDN T1 ON T1.DocEntry = T0.DocEntry
	INNER JOIN OITM T2 ON T2.ItemCode = T0.ItemCode
	GROUP BY T0.ItemCode, T0.Dscription, T2.OnHand
	ORDER BY T0.ItemCode

regards,

Raviraj

[Former Member · Accepted solution]

Thank you. I have added the DISTINCT clause, and my figures are now more accurate.

[kothandaraman_nagarajan]

Hi,

Use count function to get number of GRPO. try this query:

SELECT 

T0.ItemCode AS 'Item Code' 

, T0.Dscription AS 'Description' 

, MAX(T1.DocNum) AS 'Last Goods Receipt PO #' 

, MAX(T1.DocDate) AS 'Last Receipt Date' 

, SUM(T0.Quantity) AS 'Received since Go Live!' 

, T2.OnHand AS 'Currently On Hand' 

, COUNT(T1.DocNum) AS '# of Times Received'    --Number of Goods Receipt Purchase Orders for the given ItemCode 

FROM dbo.PDN1 T0 

INNER JOIN dbo.OPDN T1 ON T1.DocEntry = T0.DocEntry 

INNER JOIN dbo.OITM T2 ON T2.ItemCode = T0.ItemCode 

GROUP BY T0.ItemCode, T0.Dscription, T2.OnHand 

ORDER BY T0.ItemCode

Thanks & Regards,

Nagarajan

[Former Member]

Thanks Nagarajan. Your suggestion is exactly what I needed to do.