Query for Material Consumption Reports
SELECT T1.[ItemCode], T1.[Dscription],T2.[CardCode],T5.[CardName],Sum(T1.[Quantity])[Required For Month Order],Sum(T3.[Quantity])[Consumption],Sum(T1.[Quantity])-Sum(T3.[Quantity])[Net Requirement],T2.[MinOrdrQty],T2.[MinLevel],T2.[OrdrIntrvl],T6.[Name],T2.[OrdrMulti],
T2.[OnHand],T2.U_MinInvDays, ( Sum(T1.[Quantity])-Sum(T3.[Quantity]))/24 [Coverage Days]
FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry
Inner join oitm T2 on T1.ItemCode=T2.ItemCode
Inner join IGE1 T3 On T1.ItemCode=T3.ItemCode
Inner Join OIGE T4 ON T0.DocDate=T4.DocDate
inner join OCRD T5 On T2.CardCode=T5.CardCode
inner join OCYC T6 ON T2.[OrdrIntrvl]=T6.Code
WHERE (
datepart(month,T0.[DocDate] ) >= [%0] Or
datepart(month,T4=.[DocDate] )
>= [%0]) and (
datepart(month,T0.[DocDate] )
<= [%1] OR
datepart(month,T4.[DocDate] )
<= [%1])
Group By T1.[ItemCode], T1.[Dscription],T2.[OnHand], T2.[CardCode],T5.CardName, T2.[MinLevel], T2.[OrdrIntrvl],T6.Name, T2.[OrdrMulti], T2.[MinOrdrQty],T2.U_MinInvDays
-----------------------------------
/* 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
