Dear All,
I found many of the user asking the same question of Raw Material Consumption Query, here i have posted the sample sql code, you can build your Own report based upon the below script...
/* select * from oinm t1 */
DECLARE @D1 DATETIME
DECLARE @D2 DATETIME
DECLARE @D3 NVARCHAR(100)
SET @D1=/* t1.DocDATE */ '[%1]'
SET @D2=/* t1.DocDATE */ '[%2]'
SET @D3=/* T1.itemcode */ '[%3]'
SELECT x.Month AS MONTH,
X.CODE,
sum(X.Grpo)AS Grpo,
Sum(x.Prodissue)AS Production,
Diff=(sum(X.Grpo) - Sum(x.Prodissue))
FROM
(SELECT month(t1.DocDate) AS MONTH,
T1.ITEMCODE AS coDE,
CASE
WHEN TransType='20' THEN SUM(T1.INQTY)
ELSE '0'
END AS grpo,
'0' AS Prodissue
FROM OINM T1
WHERE TransType='20'
AND t1.DocDate BETWEEN @D1 AND @D2
AND t1.ItemCode=@d3
GROUP BY T1.ITEMCODE,
TransType,
month(t1.DocDate)
UNION ALL SELECT month(t1.DocDate) AS MONTH,
T1.ITEMCODE,
'0',
CASE
WHEN TransType='60' THEN SUM(T1.OUTQTY)
ELSE '0'
END AS Prodissue
FROM OINM T1
WHERE TransType='60'
AND t1.applobj='202'
AND t1.DocDate BETWEEN @D1 AND @D2
AND t1.ItemCode=@d3
GROUP BY T1.ITEMCODE,
TransType,
month(t1.DocDate)) AS X
GROUP BY X.CODE,
x.MONTH
Regards
Kennedy
