Skip to Content

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

Be the first to leave a comment
You must be Logged on to comment or reply to a post.