Skip to Content
avatar image
Former Member

Query to resolve historic OITM UDF data and stock balance.

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 06, 2017 at 10:59 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 06, 2017 at 11:01 AM

    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]
    Add comment
    10|10000 characters needed characters exceeded