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