on 05-27-2015 9:47 PM
I would like a query that can show me the last price we paid for a specific item on an invoice on a per unit basis. So it is basically like the "last price report" but for all item numbers.
A huge bonus would be if I could also get a column of data that shows the HIGHEST purchase price per unit for that item.
Thank you,
Hi,
You can try:
SELECT T0.[ItemCode], T0.[Dscription], T0.Price 'Last Actual Purchase Price ' FROM PCH1 T0
JOIN
(SELECT T1.[ItemCode], MAX(T1.Docentry) 'DocEntry'
FROM PCH1 T1
Group By T1.[ItemCode]) T ON T.DocEntry=T0.DocEntry AND T.ItemCode=T0.ItemCode
ORDER BY T0.ItemCode
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hi
if you got the solution
please close the thread
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Nadeem,
Try this query
SELECT distinct T1.[ItemCode], T1.[Dscription],T1.[unitMsr], T1.[Price]
FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.[CANCELED] = 'N' order by itemcode,price desc
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Nadeem Seedat,
Try the following:
SELECT T0.[ItemCode] AS 'Item No.', T0.[ItemName] AS 'Item Description', T0.[LastPurDat] AS 'Last Purchase Date', T0.[LastPurPrc] AS 'Last Purchase Price'
FROM [dbo].[OITM] T0
JO
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
HI Nadeem
check bellow Query and update the status
SELECT TOP 1 T0.[DocNum],T0.[DocDate], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[FreeTxt], T1.[unitMsr],T0.[Project], T1.[Quantity], T1.[Price] FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[ItemCode] =[%0] AND T0.[CANCELED] != 'Y' order by T0.[DocDate] desc
---Ramudu
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Nadeem,
Pleae try this for Last Unit Price; also note that this will be in a Purchase Request or Purchase Order in FMS:
SELECT T1.[Price] FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[ItemCode] = $[$38.1.0] ORDER BY T0.[DocEntry] Desc
And this for Last Supplier:
SELECT T0.[CardName] FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[ItemCode] = $[$38.1.0] ORDER BY T0.[DocEntry] Desc
Regards,
Isaac K.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.