Inventory Audit Report
Hi All
i am trying this query for inventory audit report .in this query i am getting stock qty of opening balance, in quantity,out qty,closing balance qty. now i want to add value of opening balance ,in qty, out qty, closing qty.
-
Declare @FromDate Datetime Declare @ToDate Datetime Declare @Group nvarchar(10) Set @FromDate = (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]') Set @ToDate = (Select max(S1.Docdate) from OINM s1 where S1.Docdate <='[%1]') Set @Group = (Select Max(s2.ItmsGrpCod) from OITB S2 Where S2.ItmsGrpNam = '[%2]') Select a.Itemcode, a.Dscription, sum(a.[Opening Balance]) as [Opening Balance], sum(a.[IN]) as [IN], Sum(a.OUT) as OUT, ((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing from( Select N1.Itemcode, N1.Dscription, (sum(N1.inqty)-sum(n1.outqty)) as [Opening Balance], 0 as [IN], 0 as OUT From OINM N1 Where N1.DocDate < @FromDate Group By N1.ItemCode,N1.Dscription Union All select N1.Itemcode, N1.Dscription, 0 as [Opening Balance], sum(N1.inqty) as [IN], 0 as OUT From OINM N1 Where N1.DocDate >= @FromDate and N1.DocDate <= @ToDate and N1.Inqty >0 Group By N1.ItemCode,N1.Dscription Union All select N1.Itemcode, N1.Dscription, 0 as [Opening Balance], 0 as [IN], sum(N1.outqty) as OUT From OINM N1 Where N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and N1.OutQty > 0 Group By N1.ItemCode,N1.Dscription)a, OITM I1 where a.ItemCode=I1.ItemCode and I1.ItmsGrpCod = @Group Group By a.Itemcode,a.Dscription
Thanks In advance
Areeba Ali