on 05-10-2024 9:21 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
8 | |
7 | |
5 | |
4 | |
3 | |
2 | |
2 | |
2 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.