Skip to Content
0
Former Member
Dec 01, 2014 at 08:53 AM

Need help on stock query itemgroup wise

56 Views

Dear Experts

Need help on Stock - OB, In, Out, CB query with itemgroup and warehouse in the output but not as selection parameter.

I am using this query for stock in and out. I need to add item group and warehouse in the same. I have gone through some threads were item group and ware house is not coming in the output but able to filter by item group/warehouse which is given as selection parameter. I dont want item group/warehouse as selection parameter, instead it needs to get shown in the output. Below is the query used

Declare @Whse nvarchar(10)

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]'

Select a.Itemcode, max(a.Dscription) as ItemName,

sum(a.OpeningBalance) as OpeningBalance, sum(a.INq) as 'IN', sum(a.OUT) as OUT,

((sum(a.OpeningBalance) + sum(a.INq)) - Sum(a.OUT)) as Closing ,

(Select i.InvntryUom from OITM i where i.ItemCode=a.Itemcode) as UOM

from( Select N1.Itemcode, N1.Dscription, (sum(N1.inqty)-sum(n1.outqty))

as OpeningBalance, 0 as INq, 0 as OUT From dbo.OINM N1

Where N1.DocDate < @FromDate Group By N1.ItemCode,

N1.Dscription Union All select N1.Itemcode, N1.Dscription, 0 as OpeningBalance,

sum(N1.inqty) , 0 as OUT From dbo.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 OpeningBalance, 0 , sum(N1.outqty) as OUT

From dbo.OINM N1 Where N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and N1.OutQty > 0

Group By N1.ItemCode,N1.Dscription) a, dbo.OITM I1

where a.ItemCode=I1.ItemCode

Group By a.Itemcode Having sum(a.OpeningBalance) + sum(a.INq) + sum(a.OUT) > 0 Order By a.Itemcode

Output I require is as below

Item group Warehouse Code Item No. ItemName OpeningBalance IN OUT Closing UOM