Query inventario de lotes a fecha determinada

[Un_Tal_Erik_J]

Alguien que me pueda ayudar con la siguiente consulta , me duplica algunas lineas

SELECT DISTINCT T0.ItemCode, T1.ItemName, T1.[ItmsGrpCod], T0.BatchNum, T0.WhsCode, T2.WhsName, T1.[InvntryUom], T3.ExpDate, T3.PrdDate, T3.InDate, T3.CreateDate ,

ISNULL((SELECT SUM(Y.Quantity) FROM IBT1 Y WHERE Y.ItemCode = T0.ItemCode AND Y.WhsCode = T0.WhsCode AND Y.BatchNum = T0.BatchNum AND Y.Direction != ‘1’ AND Y.DocDate <= ‘[%0]’),0)-ISNULL((SELECT SUM(Y.Quantity) FROM IBT1 Y WHERE Y.ItemCode = T0.ItemCode AND Y.WhsCode = T0.WhsCode AND Y.BatchNum = T0.BatchNum AND Y.Direction = ‘1’ AND Y.DocDate <= ‘[%0]’),0) ‘Stock’

FROM IBT1 T0

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

INNER JOIN OWHS T2 ON T2.WhsCode = T0.WhsCode

INNER JOIN OIBT T3 ON T3.[BatchNum] = T0.[BatchNum]

WHERE ISNULL((SELECT SUM(Y.Quantity) FROM IBT1 Y WHERE Y.ItemCode = T0.ItemCode AND Y.WhsCode = T0.WhsCode AND Y.BatchNum = T0.BatchNum AND Y.Direction != ‘1’ AND Y.DocDate <= ‘[%0]’),0)-ISNULL((SELECT SUM(Y.Quantity) FROM IBT1 Y WHERE Y.ItemCode = T0.ItemCode AND Y.WhsCode = T0.WhsCode AND Y.BatchNum = T0.BatchNum AND Y.Direction = ‘1’ AND Y.DocDate <= ‘[%0]’),0)>‘0’

[juliancab]

Creo te las duplica por el tema de los almacenes.

[Un_Tal_Erik_J]

como podría descartar esta duplicad ? alguna idea si lo ejecuto al dia el resultado es correcto si lo ejecuto a una fecha atrazada el resultado es correcto sin las lineas duplicadas que menciono

[Willy_Caldero]

creo que te falta esta relacion en tu join:
Inner Join OIBT T1 On T0.BatchNum=T1.BatchNum AND T1.ItemCode=T0.ItemCode

saludos,