TOTAL CONSUMPTION VS SALES QUERY

[bradderz]

Hi Experts i am currently in development of a query but have hit a snag with the below. it seems to be giving me false output with regards to the Consumed qty. I'm sure this is something i am doing wrong with my joins but I cannot for the life of me figure out why. please help:

SELECT
T1."ItemCode",
ISNULL(SUM(T1."IssuedQty"), 0) AS "Consumed In Production",
ISNULL(SUM(T2."Quantity"), 0) AS "Sold Quantity",
ISNULL(SUM(T1."IssuedQty"), 0) + ISNULL(SUM(T2."Quantity"), 0) AS "Total Consumption"
FROM
"WOR1" T1
LEFT JOIN
"OWOR" T0 ON T1."DocEntry" = T0."DocEntry" AND T0."Status" IN ('R', 'L')
LEFT JOIN
"INV1" T2 ON T1."ItemCode" = T2."ItemCode" AND T2."docDate" = T0.PostDate
WHERE 
T0.postdate BETWEEN '[%0]' AND '[%1]'
GROUP BY
T1."ItemCode"
ORDER BY
T1."ItemCode"

 Thanking you in advance

Brad

[BattleshipCobra · Accepted solution]

Hi Brad,

The way you are joining the tables will limit your data.  Say there are invoices on days with no production orders, those will be lost.

I used a CTE table setup here:

-- DECLARE VARIABLES

DECLARE @Date1 DATETIME
DECLARE @Date2 DATETIME
 
SET @Date1 = (SELECT MIN(S0.[DocDate]) FROM OINM S0 WHERE S0.[DocDate] >= '[%0]');
SET @Date2 = (SELECT MAX(S1.[DocDate]) FROM OINM S1 WHERE S1.[DocDate] <= '[%1]');

-- DEFINE TABLE

WITH ConsumptionData AS
(		
SELECT 
	 D0.[ItemCode]
	,D0.[DocDate] AS 'Date'
	,D0.[Quantity] AS 'Sold'
	,0 AS 'Issued'
	,D0.[Quantity] AS 'Combined'
FROM
	INV1 D0 WITH (NOLOCK)
	INNER JOIN OINV D1 WITH (NOLOCK) ON D0.[DocEntry] = D1.[DocEntry]
WHERE D1.[CANCELED] = 'N'

UNION ALL

SELECT 
	 D0.[ItemCode]
	,D0.[DocDate] AS 'Date'
	,D0.[Quantity] * -1 AS 'Sold'
	,0 AS 'Issued'
	,D0.[Quantity] * -1 AS 'Combined'
FROM
	RIN1 D0 WITH (NOLOCK)
	INNER JOIN ORIN D1 WITH (NOLOCK) ON D0.[DocEntry] = D1.[DocEntry]
WHERE D1.[CANCELED] = 'N'

UNION ALL

SELECT
	 D0.[ItemCode]
	,D1.[PostDate] AS 'Date'
	,0 AS 'Sold'
	,D0.[IssuedQty] AS 'Issued'
	,D0.[IssuedQty] AS 'Combined'
FROM
	WOR1 D0 WITH (NOLOCK)
	INNER JOIN OWOR D1 WITH (NOLOCK) ON D0.[DocEntry] = D1.[DocEntry] 
WHERE D1.[Status] IN ('R', 'L')
)

-- PULL DATA

SELECT
	 T0.[ItemCode]
	,T0.[ItemName]
	,ISNULL(SUM(X.[Issued]),0) AS 'Consumed In Production'
	,ISNULL(SUM(X.[Sold]),0) AS 'Sold Quantity'
	,ISNULL(SUM(X.[Combined]),0) AS 'Total Consumption'

FROM
	OITM T0 WITH (NOLOCK)
	LEFT JOIN ConsumptionData X ON T0.[ItemCode] = X.[ItemCode]

WHERE X.[Date] BETWEEN @Date1 AND @Date2

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

ORDER BY T0.[ItemCode]

You can also use a derived table:

-- DECLARE VARIABLES

DECLARE @Date1 DATETIME
DECLARE @Date2 DATETIME
 
SET @Date1 = (SELECT MIN(S0.[DocDate]) FROM OINM S0 WHERE S0.[DocDate] >= '[%0]')
SET @Date2 = (SELECT MAX(S1.[DocDate]) FROM OINM S1 WHERE S1.[DocDate] <= '[%1]')

-- PULL DATA

SELECT
	 T0.[ItemCode]
	,T0.[ItemName]
	,ISNULL(SUM(X.[Issued]),0) AS 'Consumed In Production'
	,ISNULL(SUM(X.[Sold]),0) AS 'Sold Quantity'
	,ISNULL(SUM(X.[Combined]),0) AS 'Total Consumption'

FROM
	OITM T0 WITH (NOLOCK)
	LEFT JOIN (
		SELECT 
			 D0.[ItemCode]
			,D0.[DocDate] AS 'Date'
			,D0.[Quantity] AS 'Sold'
			,0 AS 'Issued'
			,D0.[Quantity] AS 'Combined'
		FROM
			INV1 D0 WITH (NOLOCK)
			INNER JOIN OINV D1 WITH (NOLOCK) ON D0.[DocEntry] = D1.[DocEntry]
		WHERE D1.[CANCELED] = 'N'

		UNION ALL

		SELECT 
			 D0.[ItemCode]
			,D0.[DocDate] AS 'Date'
			,D0.[Quantity] * -1 AS 'Sold'
			,0 AS 'Issued'
			,D0.[Quantity] * -1 AS 'Combined'
		FROM
			RIN1 D0 WITH (NOLOCK)
			INNER JOIN ORIN D1 WITH (NOLOCK) ON D0.[DocEntry] = D1.[DocEntry]
		WHERE D1.[CANCELED] = 'N'

		UNION ALL

		SELECT
			 D0.[ItemCode]
			,D1.[PostDate] AS 'Date'
			,0 AS 'Sold'
			,D0.[IssuedQty] AS 'Issued'
			,D0.[IssuedQty] AS 'Combined'
		FROM
			WOR1 D0 WITH (NOLOCK)
			INNER JOIN OWOR D1 WITH (NOLOCK) ON D0.[DocEntry] = D1.[DocEntry] 
		WHERE D1.[Status] IN ('R', 'L')
	) X ON T0.[ItemCode] = X.[ItemCode]

WHERE X.[Date] BETWEEN @Date1 AND @Date2

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

ORDER BY T0.[ItemCode]

Both are roughly the same but slightly different structures.  I just stacked the three sets of rows and then group them at the end.

I hope you can follow what I'm doing.  I also added in credit notes.  But you can remove that part if you don't want it there.

I tested these and they seem to do what you want, you could further filter the item list.  The way I did it you would still get zero quantity items to give you a full list even if something wasn't consumed.  But you could just INNER join the data table or add a filter as needed.

Let me know if this does what you need,

Mike