cancel
Showing results for 
Search instead for 
Did you mean: 

Need help on stock query itemgroup wise

Former Member
0 Kudos

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 CodeItem No.ItemNameOpeningBalanceINOUTClosingUOM

Accepted Solutions (1)

Accepted Solutions (1)

Rafaee_Mustafa
Active Contributor
0 Kudos

Dear Sundaram,

Check this

--Declare @Whse nvarchar(10)

declare @FromDate date

declare @ToDate date

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.Warehouse, 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

,(SELECT T0.[ItmsGrpNam] FROM OITB T0  INNER JOIN OITM T1 ON T0.[ItmsGrpCod] = T1.[ItmsGrpCod] WHERE T1.[ItemCode] = a.ItemCode)  ItemGroup

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

as OpeningBalance, 0 as INq, 0 as OUT, n1.Warehouse

From dbo.OINM N1 Where N1.DocDate < @FromDate  Group By N1.ItemCode,N1.Dscription

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

sum(N1.inqty) , 0 as OUT ,n1.Warehouse

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

and N1.Inqty >0 Group By N1.ItemCode,N1.Dscription

,n1.Warehouse Union All  select N1.Itemcode, N1.Dscription, 0 as OpeningBalance, 0 , sum(N1.outqty) as OUT,N1.Warehouse

--,(SELECT T0.[ItmsGrpNam] FROM OITB T0  INNER JOIN OITM T1 ON T0.[ItmsGrpCod] = T1.[ItmsGrpCod] WHERE T1.[ItemCode] = n1.ItemCode)  ItemGroup

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

Group By N1.ItemCode,N1.Dscription,n1.Warehouse) a, dbo.OITM I1 where a.ItemCode=I1.ItemCode

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

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

Try this:

declare @DateT as date

declare @dateF as date

  set @dateF = /* Select a.DocDate

       from oinm a

       where a.DocDate = */ [%0]

  set @dateT = /* Select a.DocDate

      from oinm a

       where a.DocDate = */ [%1]

select

  c.ItmsGrpNam,

  a.Warehouse,

  a.ItemCode,

  b.ItemName,

  b.InvntryUom as 'UOM',

  isnull((select sum(isnull(x.InQty,0) - isnull(x.OutQty,0))

  from OINM x

  where x.DocDate < @dateF

  and x.ItemCode = a.ItemCode

  and x.Warehouse = a.Warehouse),0) as 'OB',

  (select sum(isnull(x.InQty,0) )

  from OINM x

  where x.DocDate >= @dateF

  and x.DocDate <= @DateT

  and x.ItemCode = a.ItemCode

  and x.Warehouse = a.Warehouse) as 'IN',

  (select sum(isnull(x.OutQty,0) )

  from OINM x

  where x.DocDate >= @dateF

  and x.DocDate <= @DateT

  and x.ItemCode = a.ItemCode

  and x.Warehouse = a.Warehouse) as 'OUT',

  (select sum(isnull(x.InQty,0) - isnull(x.OutQty,0))

  from OINM x

  where x.DocDate <= @DateT

  and x.ItemCode = a.ItemCode

  and x.Warehouse = a.Warehouse) as 'CB'

from OINM a

  inner join OITM b on a.ItemCode = b.ItemCode

  inner join OITB c on b.ItmsGrpCod = c.ItmsGrpCod

where

  a.docdate >= @dateF and

  a.docdate <= @DateT

group by

  c.ItmsGrpNam,

  a.Warehouse,

  a.ItemCode,

  b.ItemName,

  b.InvntryUom

order by

  a.ItemCode asc

Regards,

Iris