Query to resolve historic OITM UDF data and stock balance.

[former_member529677]

Hi, i am trying to write a query for each "ItemCode" in OITM (on a certain date) what values were set for UDF's and current stock balance.

I currently have one query which will return the 'On Hand' balance for each item code which appears to be working correctly as below:-

---

SELECT T1.WhsName, T0.ItemCode, Max(T0.Dscription) 'Item Name', SUM(T0.InQty-T0.OutQty) 'On Hand' FROM OINM T0 JOIN OWHS T1 ON T1.WhsCode=T0.Warehouse WHERE T0.DocDate <= '2016-10-20' GROUP BY T1.WhsName,T0.ItemCode Having SUM(T0.InQty-T0.OutQty) > 0

---

But struggling to write a subquery which will check the AITM table and return the top 1 data against the same itemcode for the same date or last entry up to the same date.

Please can anyone advise?

Many thanks

Alan

[KennedyT21 · Accepted solution]

Hi

Try This

SELECT T1.WhsName, T0.ItemCode, Max(T0.Dscription) 'Item Name', SUM(T0.InQty-T0.OutQty) 'On Hand' , A= (Select top 1 OnHand  from AITM K where k.itemcode=T0.Itemcode)FROM OINM T0 JOIN OWHS T1 ON T1.WhsCode=T0.Warehouse WHERE T0.DocDate <= '2016-10-20' GROUP BY T1.WhsName,T0.ItemCode Having SUM(T0.InQty-T0.OutQty) > 0

Cheers!

Kennedy

[Johan_H]

If Kennedy T's solution doesn't work, then you can try this:

SELECT T0.ItemCode
 , (SELECT TOP 1 Dscription 
        FROM OINM
        WHERE DocDate <= '2016-10-20' 
        AND  ItemCode = T0.ItemCode
GROUP BY Dscription, DocDate
        HAVING SUM(InQty-OutQty) > 0
ORDER BY DocDate DESC) 'Item Name'
     , (SELECT TOP 1 SUM(InQty-OutQty) 
        FROM OINM
        WHERE DocDate <= '2016-10-20' 
        AND  ItemCode = T0.ItemCode
GROUP BY Dscription, DocDate
        HAVING SUM(InQty-OutQty) > 0
ORDER BY DocDate DESC) 'On Hand'
     , (SELECT TOP 1 DocDate
        FROM OINM
        WHERE DocDate <= '2016-10-20' 
        AND  ItemCode = T0.ItemCode
GROUP BY Dscription, DocDate
        HAVING SUM(InQty-OutQty) > 0
ORDER BY DocDate DESC) 'DocDate'
 , (SELECT TOP 1 OWHS.WhsName
        FROM OINM INNER JOIN OWHS ON OINM.Warehouse = OWHS.WhsCode
        WHERE OINM.DocDate <= '2016-10-20' 
        AND  OINM.ItemCode = T0.ItemCode
GROUP BY OWHS.WhsName, OINM.DocDate
        HAVING SUM(OINM.InQty-OINM.OutQty) > 0
ORDER BY DocDate DESC) 'DocDate'
FROM OITM T0
WHERE T0.ItemCode = [%0]