Inventory closing stock query

[Former Member]

Hi expert,

I want query which will give me the closing stock on particular date and for particular warehouse as parameter and the details as below in query.

Item Code	Item Name	stock on that particular date.	whsname	Whscode

Regards & Thanks

Swapnil

[KennedyT21 · Accepted solution]

HI Swapnil,

try This

/* SelecT * from OINM t1 */

DECLARE @D1 DATETIME

DECLARE @D2 DATETIME

SET @D1=  /* t1.docdate */ '[%0]'

SET @D2=  /* t1.docdate */ '[%1]'

Select t1.Itemcode ,t2.itemname,t1.Warehouse,t3.whsname, Onhand=sum(inqty- outqty) from OINM t1

Inner join OITM t2 on t1.ItemCode=t2.ItemCode

inner join OWHS t3 on t3.WhsCode=t1.Warehouse

where t1.DocDate between @d1 and @d2

group by t1.itemcode,t1.Itemcode ,t2.itemname,t1.Warehouse,t3.whsname

order by t1.ItemCode,t3.whscode

Regards

Kennedy

[Former Member · Accepted solution]

Query is not working..

Regards

Swapnil

[Former Member · Accepted solution]

Hi

Try this

Put from date as initial date and to date as your closing date

Declare @FromDate Datetime
Declare @ToDate Datetime
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 @Whse = Max(s2.Warehouse) from dbo.OINM S2 Where S2.Warehouse = '[%2]'
Select @Whse as 'Warehouse', 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.Warehouse, 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 and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,
N1.Dscription Union All select N1.Warehouse, 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 and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,N1.Dscription
Union All select N1.Warehouse, 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
and N1.Warehouse = @Whse Group By N1.Warehouse,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

[KennedyT21 · Accepted solution]

Sry Try This

/* SelecT * from OINM t1 */

DECLARE @D1 DATETIME

DECLARE @D3 Nvarchar(200)

SET @D1=  /* t1.docdate */ '[%0]'

set @D3 =  /* t1.Warehouse */ '[%2]'

Select t1.Itemcode ,t2.itemname,t1.Warehouse,t3.whsname, Onhand=sum(inqty- outqty) from OINM t1

Inner join OITM t2 on t1.ItemCode=t2.ItemCode

inner join OWHS t3 on t3.WhsCode=t1.Warehouse

where t1.DocDate <= @d1 and t1.Warehouse=@D3

group by t1.itemcode,t1.Itemcode ,t2.itemname,t1.Warehouse,t3.whsname

order by t1.ItemCode,t1.Warehouse

Regards

Kennedy

[Former Member · Accepted solution]

Sir,

Your query is working fine, If the Inventory Comes in Decimal Place (6 Digits). It will be very helpful.