Detailed analysis of Inventory Movement with Values
This is a routine requirement of cost, tax and company audit, that auditors inquire about a lots of data on inventory (Consumption, Valuation Etc).
While I was struggling to produce the data that was needed by auditors, I developed below SQL, which I thought might be useful to everyone.
It is a long and time taking query, which I hope the experts here can optimize further for faster execution.
Being new to the community, it is possible that this is posted at wrong place, moderators, please move the post to appropriate place.
Please post your comments (Good or Bad, whatever)
DECLARE | @FROMDate Datetime |
DECLARE @ToDate Datetime
SELECT | @FROMDate = MIN(S0.Docdate) |
FROM dbo.OINM S0 | |
WHERE S0.Docdate >='[%0]’ |
SELECT | @ToDate = MAX(S1.Docdate) |
FROM dbo.OINM S1 | |
WHERE S1.Docdate <='[%1]’ |
–for opening balance
select | a.item1 as Item, |
sum(a.Q_OB1) as Q_OB, | |
sum(a.V_OB1) as V_OB, | |
sum(a.Q_Pur1) as Q_Pur, | |
sum(a.V_Pur1) as V_Pur, | |
sum(a.Q_Pur_Ret1) as Q_Pur_Ret, | |
sum(a.V_Pur_Ret1) as V_Pur_Ret, | |
sum(a.Q_Pur1) + sum(a.Q_Pur_Ret1) as Q_Pur_Net, | |
sum(a.V_Pur1) + sum(a.V_Pur_Ret1) as V_Pur_Net, | |
sum(a.Q_Prod_In1) as Q_Prod_In, | |
sum(a.V_Prod_In1) as V_Prod_In, | |
sum(a.Q_Prod_Out1) as Q_Prod_Out, | |
sum(a.V_Prod_Out1) as V_Prod_Out, | |
sum(a.V_Reval_Net1) as V_Reval_Net, | |
sum(a.Q_Sales1) as Q_Sales, | |
sum(a.V_Sales1) as V_Sales, | |
sum(a.Q_Sales_Ret1) as Q_Sales_Ret, | |
sum(a.V_Sales_Ret1) as V_Sales_Ret, | |
sum(a.Q_Sales1) + sum(a.Q_Sales_Ret1) as Q_Sales_Net, | |
sum(a.V_Sales1) + sum(a.V_Sales_Ret1) as V_Sales_Net, | |
sum(a.Q_OB1) + sum(a.Q_Pur1) + sum(a.Q_Pur_Ret1) + sum(a.Q_Prod_In1) + sum(a.Q_Prod_Out1) + sum(a.Q_Sales1) + sum(a.Q_Sales_Ret1) as Q_CB1, | |
sum(a.V_OB1) + sum(a.V_Pur1) + sum(a.V_Pur_Ret1) + sum(a.V_Prod_In1) + sum(a.V_Prod_Out1) + sum(a.V_Reval_Net1) + sum(a.V_Sales1) + sum(a.V_Sales_Ret1) as V_CB1, | |
sum(a.Q_Oth1) as Q_Others, | |
sum(a.V_Oth1) as V_Others |
from (
select | t0.itemcode as Item1, |
sum(t0.inqty)-sum(t0.outqty) as Q_OB1, | |
sum(t0.transvalue) as V_OB1, | |
0 as Q_Pur1, | |
0 as V_Pur1, | |
0 as Q_Pur_Ret1, | |
0 as V_Pur_Ret1, | |
0 as Q_Pur_Net1, | |
0 as V_Pur_Net1, | |
0 as Q_Prod_In1, | |
0 as V_Prod_In1, | |
0 as Q_Prod_Out1, | |
0 as V_Prod_Out1, | |
0 as V_Reval_Net1, | |
0 as Q_Sales1, | |
0 as V_Sales1, | |
0 as Q_Sales_Ret1, | |
0 as V_Sales_Ret1, | |
0 as Q_Sales_Net1, | |
0 as V_Sales_Net1, | |
0 as Q_CB1, | |
0 as V_CB1, | |
0 as Q_Oth1, | |
0 as V_Oth1 |
from oinm t0
where t0.docdate < @FROMDate
group by t0.itemcode
union all
–for purchases and landed cost additions
select | t0.itemcode as Item1, |
0 as Q_OB1, | |
0 as V_OB1, | |
sum(t0.inqty)-sum(t0.outqty) as Q_Pur1, | |
sum(t0.transvalue) as V_Pur1, | |
0 as Q_Pur_Ret1, | |
0 as V_Pur_Ret1, | |
0 as Q_Pur_Net1, | |
0 as V_Pur_Net1, | |
0 as Q_Prod_In1, | |
0 as V_Prod_In1, | |
0 as Q_Prod_Out1, | |
0 as V_Prod_Out1, | |
0 as V_Reval_Net1, | |
0 as Q_Sales1, | |
0 as V_Sales1, | |
0 as Q_Sales_Ret1, | |
0 as V_Sales_Ret1, | |
0 as Q_Sales_Net1, | |
0 as V_Sales_Net1, | |
0 as Q_CB1, | |
0 as V_CB1, | |
0 as Q_Oth1, | |
0 as V_Oth1 |
from oinm t0
where t0.docdate >= @FROMDate and t0.docdate <= @ToDate and t0.transtype in (18, 20, 69)
group by t0.itemcode
union all
–for purchase returns
select | t0.itemcode as Item1, |
0 as Q_OB1, | |
0 as V_OB1, | |
0 as Q_Pur1, | |
0 as V_Pur1, | |
sum(t0.inqty)-sum(t0.outqty) as Q_Pur_Ret1, | |
sum(t0.transvalue) as V_Pur_Ret1, | |
0 as Q_Pur_Net1, | |
0 as V_Pur_Net1, | |
0 as Q_Prod_In1, | |
0 as V_Prod_In1, | |
0 as Q_Prod_Out1, | |
0 as V_Prod_Out1, | |
0 as V_Reval_Net1, | |
0 as Q_Sales1, | |
0 as V_Sales1, | |
0 as Q_Sales_Ret1, | |
0 as V_Sales_Ret1, | |
0 as Q_Sales_Net1, | |
0as V_Sales_Net1, | |
0 as Q_CB1, | |
0 as V_CB1, | |
0 as Q_Oth1, | |
0 as V_Oth1 |
from oinm t0
where t0.docdate >= @FROMDate and t0.docdate <= @ToDate and t0.transtype in (19, 21)
group by t0.itemcode
union all
–for receipt from production
select | t0.itemcode as Item1, |
0 as Q_OB1, | |
0 as V_OB1, | |
0 as Q_Pur1, | |
0 as V_Pur1, | |
0 as Q_Pur_Ret1, | |
0 as V_Pur_Ret1, | |
0 as Q_Pur_Net1, | |
0 as V_Pur_Net1, | |
sum(t0.inqty) – sum(t0.outqty) as Q_Prod_In1, | |
sum(t0.transvalue) as V_Prod_In1, | |
0 as Q_Prod_Out1, | |
0 as V_Prod_Out1, | |
0 as V_Reval_Net1, | |
0 as Q_Sales1, | |
0 as V_Sales1, | |
0 as Q_Sales_Ret1, | |
0 as V_Sales_Ret1, | |
0 as Q_Sales_Net1, | |
0 as V_Sales_Net1, | |
0 as Q_CB1, | |
0 as V_CB1, | |
0 as Q_Oth1, | |
0 as V_Oth1 |
from oinm t0
where t0.docdate >= @FROMDate and t0.docdate <= @ToDate and t0.transtype in (59)
group by t0.itemcode
union all
–for issue to production
select | t0.itemcode as Item1, |
0 as Q_OB1, | |
0 as V_OB1, | |
0 as Q_Pur1, | |
0 as V_Pur1, | |
0 as Q_Pur_Ret1, | |
0 as V_Pur_Ret1, | |
0 as Q_Pur_Net1, | |
0 as V_Pur_Net1, | |
0 as Q_Prod_In1, | |
0 as V_Prod_In1, | |
sum(t0.inqty) – sum(t0.outqty) as Q_Prod_Out1, | |
sum(t0.transvalue) as V_Prod_Out1, | |
0 as V_Reval_Net1, | |
0 as Q_Sales1, | |
0 as V_Sales1, | |
0 as Q_Sales_Ret1, | |
0 as V_Sales_Ret1, | |
0 as Q_Sales_Net1, | |
0 as V_Sales_Net1, | |
0 as Q_CB1, | |
0 as V_CB1, | |
0 as Q_Oth1, | |
0 as V_Oth1 |
from oinm t0
where t0.docdate >= @FROMDate and t0.docdate <= @ToDate and t0.transtype in (60)
group by t0.itemcode
union all
–for value change due to inventory revaluation and production variance
select | t0.itemcode as Item1, |
0 as Q_OB1, | |
0 as V_OB1, | |
0 as Q_Pur1, | |
0 as V_Pur1, | |
0 as Q_Pur_Ret1, | |
0 as V_Pur_Ret1, | |
0 as Q_Pur_Net1, | |
0 as V_Pur_Net1, | |
0 as Q_Prod_In1, | |
0 as V_Prod_In1, | |
0 as Q_Prod_Out1, | |
0 as V_Prod_Out1, | |
sum(t0.transvalue) as V_Reval_Net1, | |
0 as Q_Sales1, | |
0 as V_Sales1, | |
0 as Q_Sales_Ret1, | |
0 as V_Sales_Ret1, | |
0 as Q_Sales_Net1, | |
0 as V_Sales_Net1, | |
0 as Q_CB1, | |
0 as V_CB1, | |
0 as Q_Oth1, | |
0 as V_Oth1 |
from oinm t0
where t0.docdate >= @FROMDate and t0.docdate <= @ToDate and t0.transtype in (162, 202)
group by t0.itemcode
union all
–for sales
select | t0.itemcode as Item1, |
0 as Q_OB1, | |
0 as V_OB1, | |
0 as Q_Pur1, | |
0 as V_Pur1, | |
0 as Q_Pur_Ret1, | |
0 as V_Pur_Ret1, | |
0 as Q_Pur_Net1, | |
0 as V_Pur_Net1, | |
0 as Q_Prod_In1, | |
0 as V_Prod_In1, | |
0 as Q_Prod_Out1, | |
0 as V_Prod_Out1, | |
0 as V_Reval_Net1, | |
sum(t0.outqty) as Q_Sales1, | |
sum(t0.transvalue) as V_Sales1, | |
0 as Q_Sales_Ret1, | |
0 as V_Sales_Ret1, | |
0 as Q_Sales_Net1, | |
0 as V_Sales_Net1, | |
0 as Q_CB1, | |
0 as V_CB1, | |
0 as Q_Oth1, | |
0 as V_Oth1 |
from oinm t0
where t0.docdate >= @FROMDate and t0.docdate <= @ToDate and t0.transtype in (15)
group by t0.itemcode
union all
–for sales return
select | t0.itemcode as Item1, |
0 as Q_OB1, | |
0 as V_OB1, | |
0 as Q_Pur1, | |
0 as V_Pur1, | |
0 as Q_Pur_Ret1, | |
0 as V_Pur_Ret1, | |
0 as Q_Pur_Net1, | |
0 as V_Pur_Net1, | |
0 as Q_Prod_In1, | |
0 as V_Prod_In1, | |
0 as Q_Prod_Out1, | |
0 as V_Prod_Out1, | |
0 as V_Reval_Net1, | |
0 as Q_Sales1, | |
0 as V_Sales1, | |
sum(t0.outqty) as Q_Sales_Ret1, | |
sum(t0.transvalue) as V_Sales_Ret1, | |
0 as Q_Sales_Net1, | |
0 as V_Sales_Net1, | |
0 as Q_CB1, | |
0 as V_CB1, | |
0 as Q_Oth1, | |
0 as V_Oth1 |
from oinm t0
where t0.docdate >= @FROMDate and t0.docdate <= @ToDate and t0.transtype not in (14, 15, 18, 20, 69, 19, 21, 59, 60, 162, 202)
group by t0.itemcode
) a
group by a.item1
order by a.item1