2

Hi I have the following data set generated from database (of a SAP Business One system)

Dataset

I generated this result by using the following query:

SELECT T1.[ItemCode], T0.[DocNum], T1.[Price], T0.[CardName] 
FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
GROUP BY T1.[ItemCode], T1.[Price], T0.[CardName],T0.[DocNum]

I want to grab the latest price of each item for each customer. Can anyone please help me with that? Thanks in Advance

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Parmeet Singh
  • 23
  • 3

2 Answers2

0

You can use row_number()

select * from
(
SELECT T1.[ItemCode], T0.[DocNum], T1.[Price], T0.[CardName],
row_number() over(partition by T0.[CardName],T1.[ItemCode] order by T1.[Price] desc) as rn
FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
)A where rn=1
Fahmi
  • 37,315
  • 5
  • 22
  • 31
0

You should not use OINV.CardName as there can be different cardnames for the same customer in OINV-table, if you change the name in BP-masterdata, or the same name for multiple businesspartners. Use T0.CardCode or join OCRD on OINV.CardCode and, if required, get the name from OCRD.

Also, if you order by price, you only get the lowest but not the latest price, so if you want to order it, go for OINV.DocDate DESC or OINV.DocEntry DESC to get the price from latest by date or by added document.

Ramil Aliyev 007
  • 4,437
  • 2
  • 31
  • 47
Tom79
  • 1