SQL: display newest entry

Question

I'm not really much of a developer, I only started self-learning VBA for Excel this year and I only started working with SQL two days ago...

That said: I'm trying to write a query that only shows the newest entry for each INVBTICKETNO based on ITTRANDATE but I can't seem to figure it out.

SELECT
  WHSNAME.WHSNNAME AS [Warehouse Name]
 ,VALID_BIN.BIN_TYPE AS T
 ,INVENTORY_BIN.INVBBINNO AS [Bin Number]
 ,INVENTORY_BIN.INVBITEMNO AS [Item Number]
 ,INVENTORY_ITEM_TAB.DESCR AS [Item Description]
 ,PRODUCT_CODE.DESCR AS [Product Code & Description]
 ,CUSTOMER.CSNAME AS [Customer Name]
 ,ORDERS.ORDER_NO AS [Oder No]
 ,INVENTORY_BIN.DATE_OF_INVENTORY AS [Date of Inventory]
 ,INVENTORY_BIN.INVBTICKETNO AS [Ticket No]
 ,INVENTORY_BIN.INVBQTYONHAND AS [Qty on Hand]
 ,ORDERS.QTY_ORDERED AS [Qty Ordered]
 ,INVENTORY_ITEM_TAB.SQUARE_FEET_UNIT AS MSF
 ,INVENTORY_BIN.EXTENDED_CST AS [Valuation?]
 ,INVENTORY_TRAN.ITSOURCE AS Source
 ,INVENTORY_TRAN.ITCOMMENT AS Comment
 ,MAX(INVENTORY_TRAN.ITTRANDATE) AS [Trans Date]
 ,ORDERS.FOR_INVT_FLG AS Flag
 ,SALESAGENT.SANAME AS [Sales Agent]
 ,ORDERS.CUST_PO_NO AS [Customer PO No]
FROM dbo.INVENTORY_BIN INVENTORY_BIN
INNER JOIN dbo.INVENTORY_ITEM_TAB INVENTORY_ITEM_TAB
  ON INVENTORY_BIN.INVBITEMNO = INVENTORY_ITEM_TAB.ITEM_NO
INNER JOIN dbo.WHSNAME WHSNAME
  ON INVENTORY_BIN.INVBWHSNCODE = WHSNAME.WHSNCODE
INNER JOIN dbo.VALID_BIN VALID_BIN
  ON INVENTORY_BIN.INVBBINNO = VALID_BIN.BIN_ID
    AND INVENTORY_BIN.INVBWHSNCODE = VALID_BIN.WHSNCODE
LEFT OUTER JOIN dbo.INVENTORY_TRAN INVENTORY_TRAN
  ON INVENTORY_BIN.INVBTICKETNO = INVENTORY_TRAN.ITTICKETNO
    AND INVENTORY_BIN.DATE_LST_ACTIVITY < INVENTORY_TRAN.ITTRANDATE
LEFT OUTER JOIN dbo.CUSTOMER CUSTOMER
  ON INVENTORY_BIN.CSCODE = CUSTOMER.CSCODE
LEFT OUTER JOIN dbo.ORDERS ORDERS
  ON INVENTORY_BIN.ORDER_NO = ORDERS.ORDER_NO
INNER JOIN dbo.PRODUCT_CODE PRODUCT_CODE
  ON INVENTORY_ITEM_TAB.PROD_CD = PRODUCT_CODE.PROD_CD
LEFT OUTER JOIN dbo.SALESAGENT SALESAGENT
  ON CUSTOMER.SACODE = SALESAGENT.SACODE
WHERE ORDERS.FOR_INVT_FLG = 'R'
GROUP BY INVENTORY_BIN.INVBITEMNO
        ,INVENTORY_BIN.INVBBINNO
        ,INVENTORY_BIN.INVBQTYONHAND
        ,INVENTORY_BIN.DATE_OF_INVENTORY
        ,INVENTORY_BIN.EXTENDED_CST
        ,INVENTORY_ITEM_TAB.DESCR
        ,INVENTORY_ITEM_TAB.SQUARE_FEET_UNIT
        ,WHSNAME.WHSNNAME
        ,VALID_BIN.BIN_TYPE
        ,INVENTORY_TRAN.ITSOURCE
        ,CUSTOMER.CSNAME
        ,ORDERS.ORDER_NO
        ,ORDERS.CUST_PO_NO
        ,ORDERS.QTY_ORDERED
        ,PRODUCT_CODE.DESCR
        ,SALESAGENT.SANAME
        ,ORDERS.FOR_INVT_FLG
        ,INVENTORY_BIN.INVBTICKETNO
        ,INVENTORY_TRAN.ITCOMMENT
ORDER BY [Date of Inventory]

Answer 1

I found https://kristiannielsen.livejournal.com/6745.html and implemented their "fastest" solution.

Except it doesn't seem very fast... 1 min 12 seconds for 217 rows returned. Do queries run slower if you're working remotely?

SELECT
  WHSNAME.WHSNNAME AS [Warehouse Name]
 ,VALID_BIN.BIN_TYPE AS T
 ,INVENTORY_BIN.INVBBINNO AS [Bin Number]
 ,INVENTORY_BIN.INVBITEMNO AS [Item Number]
 ,INVENTORY_ITEM_TAB.DESCR AS [Item Description]
 ,PRODUCT_CODE.DESCR AS [Product Code & Description]
 ,CUSTOMER.CSNAME AS [Customer Name]
 ,ORDERS.ORDER_NO AS [Order Number]
 ,INVENTORY_BIN.DATE_OF_INVENTORY AS [Date of Inventory]
 ,tra1.ITTICKETNO AS [Ticket Number]
 ,INVENTORY_BIN.INVBQTYONHAND AS [Qty on Hand]
 ,ORDERS.QTY_ORDERED AS [Qty Ordered]
 ,INVENTORY_ITEM_TAB.SQUARE_FEET_UNIT AS MSF
 ,INVENTORY_BIN.EXTENDED_CST AS [Valuation?]
 ,tra1.ITSOURCE AS Source
 ,tra1.ITCOMMENT AS Comment
 ,tra1.ITTRANDATE AS [Trans Date]
 ,ORDERS.FOR_INVT_FLG AS Flag
 ,SALESAGENT.SANAME AS [Sales Agent]
 ,ORDERS.CUST_PO_NO AS [Customer PO Number]
FROM dbo.INVENTORY_TRAN tra1
**INNER JOIN (SELECT
    INVENTORY_TRAN.ITTICKETNO
   ,MAX(INVENTORY_TRAN.ITTRANDATE) AS ITTRANDATE
  FROM dbo.INVENTORY_TRAN
  GROUP BY INVENTORY_TRAN.ITTICKETNO) tra2
  ON tra1.ITTICKETNO = tra2.ITTICKETNO
    AND tra1.ITTRANDATE = tra2.ITTRANDATE**
INNER JOIN dbo.INVENTORY_BIN
  ON tra1.ITTICKETNO = INVENTORY_BIN.INVBTICKETNO
INNER JOIN dbo.INVENTORY_ITEM_TAB
  ON INVENTORY_BIN.INVBITEMNO = INVENTORY_ITEM_TAB.ITEM_NO
INNER JOIN dbo.PRODUCT_CODE
  ON INVENTORY_ITEM_TAB.PROD_CD = PRODUCT_CODE.PROD_CD
INNER JOIN dbo.ORDERS
  ON INVENTORY_BIN.ORDER_NO = ORDERS.ORDER_NO
INNER JOIN dbo.WHSNAME
  ON INVENTORY_BIN.INVBWHSNCODE = WHSNAME.WHSNCODE
INNER JOIN dbo.CUSTOMER
  ON INVENTORY_BIN.CSCODE = CUSTOMER.CSCODE
INNER JOIN dbo.SALESAGENT
  ON CUSTOMER.SACODE = SALESAGENT.SACODE
INNER JOIN dbo.VALID_BIN
  ON INVENTORY_BIN.INVBWHSNCODE = VALID_BIN.WHSNCODE
    AND VALID_BIN.BIN_ID = INVENTORY_BIN.INVBBINNO
WHERE ORDERS.FOR_INVT_FLG = 'R'
ORDER BY [Date of Inventory]

Answer 2

Replace

LEFT OUTER JOIN dbo.INVENTORY_TRAN INVENTORY_TRAN
  ON INVENTORY_BIN.INVBTICKETNO = INVENTORY_TRAN.ITTICKETNO
    AND INVENTORY_BIN.DATE_LST_ACTIVITY < INVENTORY_TRAN.ITTRANDATE

then add the columns in a sub select, which determines the comment-row with the most recent date:

INNER JOIN (SELECT
   INVENTORY_TRAN.ITTICKETNO
   ,INVENTORY_TRAN.ITSOURCE AS [Source]
   ,INVENTORY_TRAN.ITCOMMENT AS [Comment]
   ,MAX(INVENTORY_TRAN.ITTRANDATE) [Trans Date]
FROM [INVENTORY_TRAN]
GROUP BY INVENTORY_TRAN.ITTICKETNO, INVENTORY_TRAN.ITSOURCE, INVENTORY_TRAN.ITCOMMENT
HAVING MAX(INVENTORY_TRAN.ITTRANDATE) = INVENTORY_TRAN.ITTRANDATE
) AS [sub_INVENTORY_TRAN] ON [INVENTORY_BIN].[INVBTICKETNO] = [sub_INVENTORY_TRAN].[ITTICKETNO]

You also need to replace the selected columns with the new added through the sub select:

   ,[sub_INVENTORY_TRAN].[Source]
   ,[sub_INVENTORY_TRAN].[Comment]
   ,[sub_INVENTORY_TRAN].[Trans Date]

Answer 3

I rebuilt the query from scratch and it seems to work, though I don't have nearly all the columns added back in yet.

SELECT
  MAX(INVENTORY_TRAN.ITTRANDATE) AS expr1
 ,INVENTORY_BIN.INVBTICKETNO
 ,ORDERS.ORDER_NO
 ,ORDERS.FOR_INVT_FLG
FROM dbo.INVENTORY_TRAN
INNER JOIN dbo.INVENTORY_BIN
  ON INVENTORY_TRAN.ITTICKETNO = INVENTORY_BIN.INVBTICKETNO
INNER JOIN dbo.ORDERS
  ON INVENTORY_BIN.ORDER_NO = ORDERS.ORDER_NO
WHERE ORDERS.FOR_INVT_FLG = 'r'
GROUP BY INVENTORY_BIN.INVBTICKETNO
        ,ORDERS.ORDER_NO
        ,ORDERS.FOR_INVT_FLG
ORDER BY INVENTORY_BIN.INVBTICKETNO