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