SQL query: availability check

former_member1236455 — Wed, 09 Mar 2022 19:05:40 GMT

Hi,

I'm trying to create a simple availability check for quotation items, but my problem is that the open quantities ([OpenCreQty]) of the same items distributed on different positions are not summed and matched with the stock.

I have tried to solve this by equating with the sum of the open quantity, but unfortunately this does not work yet.

Has anyone an idea to fix this simple query?

Thank you for your hint in advance.

SELECT T1.[U_position], T1.[ItemCode], T1.[Quantity], T1.[OpenCreQty], T2.[OnHand]

FROM OQUT T0

INNER JOIN QUT1 T1 ON T0.[DocEntry] = T1.[DocEntry]

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

WHERE T0.[DocNum] = '332008' AND T2.[OnHand] < (SELECT SUM(T1.[OpenCreQty]) FROM OQUT T0 INNER JOIN QUT1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocNum] = '332008' GROUP BY T1.[OpenCreQty]) AND T2.[InvntItem] = 'Y'

GROUP BY T1.[U_position], T1.[ItemCode], T1.[Quantity], T1.[OpenCreQty], T2.[OnHand]

topic SQL query: availability check in Application Development and Automation Discussions

SQL query: availability check