Query for batchwise remaining stock

[Former Member]

Hello

i create the following query that show remaining quantity in that batch the quer are as,

SELECT T0.[ItemCode], T0.[Quantity], T0.[WhsCode], T3.[ItmsGrpNam], T1.[DistNumber] FROM OBTQ T0 INNER JOIN OBTN T1 ON T0.AbsEntry = T1.AbsEntry INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod WHERE T0.[WhsCode] ='WH01' ORDER BY T3.[ItmsGrpNam], T0.[ItemCode]

but i want to more field in that query that it should show purchase price for that batch & remaining quantity

the qurey should display

Item code , Remaining Quantity, Warehouse,ItemGrpName,batch Num, Purchase Price

[Former Member]

Hi,

Try:

SELECT T0.ItemCode, (T0.Quantity-T0.CommitQty) 'Remaining Qty', T0.WhsCode, T3.ItmsGrpNam, T1.DistNumber,T2.LastPurPrc

FROM OBTQ T0

INNER JOIN OBTN T1 ON T0.AbsEntry = T1.AbsEntry

INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode

INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod

WHERE T0.WhsCode ='WH01'

ORDER BY T3.ItmsGrpNam, T0.ItemCode

Thanks,

Gordon

[Former Member]

Hello

When i run this query result show last purchase price but i want actual purchase price above that batch purchase ie batchwise purchase price for the item

[Former Member]

Hello Gordon,

When i run this query result show last purchase price but i want actual purchase price above that batch purchase ie batchwise purchase price for the item

please rply me i need urgent solution...

[Former Member]

Sorry that I don't have batch activated db to access to help you.

[RohitKumar1]

SELECT T0."ItemCode",T1."ItemName", T2."ItmsGrpNam" ,
T0."WhsCode", T0."OnHand" "Total Stock", T1."ManBtchNum",T3."Quantity" "Batch Quantity",
T4."DistNumber" "Batch Number",T4."MnfDate",T4."ExpDate"
--,T4."U_Retest"
FROM OITW T0
INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"
INNER JOIN OITB T2 ON T1."ItmsGrpCod" = T2."ItmsGrpCod"
Left JOIN OBTQ T3 ON T3."ItemCode" = T0."ItemCode" AND T3."WhsCode" = T0."WhsCode"
LEFT JOIN OBTN T4 on T4."AbsEntry" = T3."MdAbsEntry" AND T4."ItemCode" = T3."ItemCode"
and T3."Quantity" >0
WHERE T0."OnHand" >0
Order By T0."ItemCode"
--and T1."ManBtchNum" = 'N'