Skip to Content

query in sap b1 for last transaction date for that particular part code.

HI sir,

Please, could you create a report for me I need new column added with the last transaction date for that particular part code on below query?

SELECT T0.[ItemCode], T0.[ItemName], T1.[WhsCode], T1.[OnHand], T1.[AvgPrice], T1.[StockValue] FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode WHERE T1.[OnHand] > 0 and T1.[createDate] <= [%0] ORDER BY T1.[ItemCode]

Regards,

Datta P.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jul 14, 2017 at 02:12 PM

    Hi,

    Try this query,

    SELECT T0.[ItemCode], T0.[ItemName], T1.[WhsCode], T1.[OnHand], T1.[AvgPrice], T1.[StockValue], T0.[LastPurDat], T0.[LstSalDate] FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode WHERE T1.[OnHand] > 0 and T1.[createDate] <= [%0] ORDER BY T1.[ItemCode]

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

    • thanks sir

      SELECT T0.[ItemCode], T0.[ItemName], T1.[WhsCode], T1.[OnHand], T1.[AvgPrice], T1.[StockValue] , (SELECT MAX(x.DocDate) FROM ((SELECT top 1 z.[DocDate] FROM ODLN z INNER JOIN DLN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc) UNION ALL (SELECT top 1 z.[DocDate] FROM OINV z INNER JOIN INV1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC) UNION ALL (SELECT top 1 z.[DocDate] FROM OPCH z INNER JOIN PCH1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC) UNION ALL (SELECT top 1 z.[DocDate] FROM OPDN z INNER JOIN PDN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC) UNION ALL (SELECT top 1 z.[DocDate] FROM ORDN z INNER JOIN RDN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC) UNION ALL (SELECT top 1 z.[DocDate] FROM ORIN z INNER JOIN RIN1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC) UNION ALL (SELECT top 1 z.[DocDate] FROM ORPC z INNER JOIN RPC1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC) UNION ALL (SELECT top 1 z.[DocDate] FROM ORPD z INNER JOIN RPD1 Tx ON z.[DocEntry] = Tx.[DocEntry] WHERE Tx.[ItemCode] = T0.[ItemCode] ORDER BY z.[DocDate] desc,z.[UpdateTS] DESC) ) x) 'Date last used' FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode WHERE T1.[OnHand] > 0 ORDER BY T1.[ItemCode]