Skip to Content
0
Former Member
Jun 01, 2015 at 05:24 AM

Need closing balance of inventory in warehouse

53 Views

Hi experts,

I need modification in this query, need to add closing balance of inventory stock in all warehouse, and also need from date and to date. In this query it showing IN qty and OUT qty in same table need to separate and also add closing balance of stock.

Declare @DocDate DateTime

Select @DocDate = Max(AA.DocDate) from OINM AA Where AA.DocDate<=[%0]

Set @DocDate = '[%0]'

SELECT T0.[ItemCode], T1.[ItemName], T0.[Warehouse], T3.WhsName

,T2.FormatCode,T2.AcctName--, T0.InvntAct

,@DocDate[Stock As on Date]

,Sum(T0.[InQty]-T0.[OutQty])[Qty]

,Sum(T0.[TransValue]) [Value]

FROM OINM T0

INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]

Left Outer Join OACT T2 on T0.InvntAct = T2.AcctCode

Inner join OWHS T3 on T0.Warehouse = T3.WhsCode

WHERE T0.[DocDate] <=@DocDate

GROUP BY T0.[ItemCode], T1.[ItemName], T0.[Warehouse], T3.WhsName--, T0.InvntAct

,T2.FormatCode ,T2.AcctName

Having ABS(Sum(T0.[InQty]-T0.[OutQty]))+ABS(Sum(T0.[TransValue]))>0

Regards,

J.vigneshwaran