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

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply