SAP Business One Last Prices Query
SQL queries to find the most recent price charged for a product to a specific customer, using invoice history.
These are sample queries you can run on Manager Boost. Use them as-is, or edit and customize on our platform. Go custom or use our pre-built queries — you're in control.
Key Concepts
Last Price vs Price List
Price lists (ITM1, ITM2) show what you could charge. Invoice lines (INV1) show what you actually charged. The last price from invoices reflects real transactions, including discounts and special pricing.
Using ROW_NUMBER()
To get the most recent price, use ROW_NUMBER() partitioned by customer and item, ordered by invoice date descending. Filter ROW_NUMBER() = 1 to get only the latest transaction.
Joining via DocEntry
INV1 links to OINV via DocEntry (the primary key). Always use DocEntry for joins — it's indexed and guaranteed unique. Use OINV to get invoice date and customer code.
Price Field on INV1
Price on INV1 is the unit price after line-level discounts. This is the actual price charged per unit. LineTotal is the total for that line (Price × Quantity).
Filtering Cancelled Invoices
Always join OINV and filter CANCELED = 'N' to exclude cancelled invoices. Cancelled invoices stay in the database but shouldn't be used for price history.
Available Fields Reference
INV1 (Invoice Lines)
DocEntry LineNum ItemCode Dscription Quantity Price LineTotal DiscPrcnt
OINV (Invoice Header)
DocEntry DocNum DocDate CardCode CardName CANCELED
OITM (Item Master)
ItemCode ItemName ItmsGrpCod
Last Price of Product to Customer
Get the most recent price charged for a specific product to a specific customer. Uses ROW_NUMBER() to identify the latest invoice line.
SELECT
T1.ItemCode,
T2.ItemName,
T0.CardCode,
T0.CardName,
T1.Price AS LastPrice,
T1.Quantity,
T0.DocDate AS LastInvoiceDate,
T0.DocNum AS LastInvoiceNum
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.CardCode = 'C10000'
AND T1.ItemCode = 'A00001'
AND T0.CANCELED = 'N'
AND (
SELECT ROW_NUMBER() OVER (
PARTITION BY T1.ItemCode, T0.CardCode
ORDER BY T0.DocDate DESC, T0.DocEntry DESC
)
) = 1Note: The above query uses a subquery for ROW_NUMBER which may not work in all SQL Server versions. Use the CTE version below for better compatibility.
WITH RankedPrices AS (
SELECT
T0.CardCode,
T0.CardName,
T1.ItemCode,
T2.ItemName,
T1.Price,
T1.Quantity,
T0.DocDate,
T0.DocNum,
ROW_NUMBER() OVER (
PARTITION BY T1.ItemCode, T0.CardCode
ORDER BY T0.DocDate DESC, T0.DocEntry DESC
) AS RowNum
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.CANCELED = 'N'
)
SELECT
CardCode,
CardName,
ItemCode,
ItemName,
Price AS LastPrice,
Quantity,
DocDate AS LastInvoiceDate,
DocNum AS LastInvoiceNum
FROM RankedPrices
WHERE CardCode = 'C10000'
AND ItemCode = 'A00001'
AND RowNum = 1| CardCode | CardName | ItemCode | ItemName | LastPrice | LastInvoiceDate | LastInvoiceNum |
|---|---|---|---|---|---|---|
| C10000 | Acme Corp | A00001 | Widget Pro | 450.00 | 2024-01-15 | 12345 |
All Last Prices for a Customer
Get the most recent price for every product ever sold to a customer. Useful for pricing analysis and customer-specific price lists.
WITH RankedPrices AS (
SELECT
T0.CardCode,
T0.CardName,
T1.ItemCode,
T2.ItemName,
T1.Price,
T1.Quantity,
T0.DocDate,
T0.DocNum,
ROW_NUMBER() OVER (
PARTITION BY T1.ItemCode, T0.CardCode
ORDER BY T0.DocDate DESC, T0.DocEntry DESC
) AS RowNum
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.CardCode = 'C10000'
AND T0.CANCELED = 'N'
)
SELECT
ItemCode,
ItemName,
Price AS LastPrice,
Quantity AS LastQuantity,
DocDate AS LastInvoiceDate,
DocNum AS LastInvoiceNum
FROM RankedPrices
WHERE RowNum = 1
ORDER BY ItemCode| ItemCode | ItemName | LastPrice | LastQuantity | LastInvoiceDate |
|---|---|---|---|---|
| A00001 | Widget Pro | 450.00 | 10 | 2024-01-15 |
| A00002 | Gadget Plus | 275.00 | 5 | 2024-01-10 |
Price History Over Time
Track how prices changed for a product-customer combination. Shows all historical prices, not just the latest.
SELECT
T0.DocDate,
T0.DocNum,
T0.CardCode,
T0.CardName,
T1.ItemCode,
T2.ItemName,
T1.Price,
T1.Quantity,
T1.LineTotal
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.CardCode = 'C10000'
AND T1.ItemCode = 'A00001'
AND T0.CANCELED = 'N'
ORDER BY T0.DocDate DESC, T0.DocEntry DESC| DocDate | DocNum | Price | Quantity | LineTotal |
|---|---|---|---|---|
| 2024-01-15 | 12345 | 450.00 | 10 | 4500.00 |
| 2023-12-10 | 12200 | 425.00 | 8 | 3400.00 |
| 2023-11-05 | 12050 | 400.00 | 12 | 4800.00 |
Tip: This shows all prices chronologically. To see only price changes, add logic to compare each row to the previous row's price.