SAP Business One Sales Analysis Query
SQL queries to analyze sales by customer — with credit notes deducted for accurate net revenue.
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
Why Include Credit Notes?
OINV alone overstates revenue. If a customer buys $50K but returns $10K, their real value is $40K. Credit notes (ORIN) must be subtracted for accurate analysis.
Excluding Tax
DocTotal includes VAT/tax. For product revenue analysis, always subtract VatSum to get net revenue — tax is pass-through, not real revenue.
UNION ALL Pattern
We combine OINV and ORIN using UNION ALL, with ORIN amounts negated. This creates a single dataset where credits subtract from invoices automatically when summed.
Available Fields Reference
OINV / ORIN (Documents)
DocEntry DocNum DocDate CardCode CardName DocTotal VatSum SlpCode CANCELED
OCRD (Customer)
CardCode CardName GroupCode SlpCode Territory
Top Customers (Simple)
Without returns — useful for quick checks but not accurate:
SELECT TOP 20
T0.CardCode,
T0.CardName,
COUNT(T1.DocNum) AS Invoices,
SUM(T1.DocTotal - T1.VatSum) AS NetRevenue
FROM OCRD T0
INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode
WHERE T1.CANCELED = 'N'
AND YEAR(T1.DocDate) = YEAR(GETDATE())
GROUP BY T0.CardCode, T0.CardName
ORDER BY NetRevenue DESC| CardCode | CardName | Invoices | NetRevenue |
|---|---|---|---|
| C10045 | Acme Corp | 28 | 156000.00 |
Limitation: Ignores returns. A customer with lots of credit notes will rank higher than they should.
Top Customers (With Returns)
UNION invoices and credit notes, subtract credits for true net revenue:
SELECT TOP 20
CardCode,
CardName,
SUM(CASE WHEN DocType = 'INV' THEN 1 ELSE 0 END) AS Invoices,
SUM(CASE WHEN DocType = 'CRN' THEN 1 ELSE 0 END) AS CreditNotes,
SUM(NetAmount) AS NetRevenue
FROM (
-- Invoices (positive)
SELECT T0.CardCode, T0.CardName, 'INV' AS DocType,
(T1.DocTotal - T1.VatSum) AS NetAmount
FROM OCRD T0
INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode
WHERE T1.CANCELED = 'N'
AND YEAR(T1.DocDate) = YEAR(GETDATE())
UNION ALL
-- Credit Notes (negative)
SELECT T0.CardCode, T0.CardName, 'CRN' AS DocType,
-(T1.DocTotal - T1.VatSum) AS NetAmount
FROM OCRD T0
INNER JOIN ORIN T1 ON T0.CardCode = T1.CardCode
WHERE T1.CANCELED = 'N'
AND YEAR(T1.DocDate) = YEAR(GETDATE())
) Combined
GROUP BY CardCode, CardName
ORDER BY NetRevenue DESC| CardCode | CardName | Invoices | CreditNotes | NetRevenue |
|---|---|---|---|---|
| C10045 | Acme Corp | 28 | 2 | 148500.00 |
Revenue Breakdown with Percentage
Each customer's share of total revenue:
WITH CustomerSales AS (
SELECT
CardCode,
CardName,
SUM(NetAmount) AS NetRevenue
FROM (
SELECT T0.CardCode, T0.CardName, (T1.DocTotal - T1.VatSum) AS NetAmount
FROM OCRD T0 INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode
WHERE T1.CANCELED = 'N' AND YEAR(T1.DocDate) = YEAR(GETDATE())
UNION ALL
SELECT T0.CardCode, T0.CardName, -(T1.DocTotal - T1.VatSum)
FROM OCRD T0 INNER JOIN ORIN T1 ON T0.CardCode = T1.CardCode
WHERE T1.CANCELED = 'N' AND YEAR(T1.DocDate) = YEAR(GETDATE())
) Combined
GROUP BY CardCode, CardName
)
SELECT
CardCode,
CardName,
NetRevenue,
ROUND(NetRevenue * 100.0 / SUM(NetRevenue) OVER(), 2) AS PctOfTotal
FROM CustomerSales
WHERE NetRevenue > 0
ORDER BY NetRevenue DESC| CardCode | CardName | NetRevenue | PctOfTotal |
|---|---|---|---|
| C10045 | Acme Corp | 148500.00 | 12.5 |
Customers with High Returns
Flag customers where credit notes exceed a threshold:
SELECT
CardCode,
CardName,
Invoices,
CreditNotes,
GrossRevenue,
Returns,
GrossRevenue - Returns AS NetRevenue,
ROUND(Returns * 100.0 / NULLIF(GrossRevenue, 0), 1) AS ReturnPct
FROM (
SELECT
T0.CardCode,
T0.CardName,
SUM(CASE WHEN src = 'INV' THEN 1 ELSE 0 END) AS Invoices,
SUM(CASE WHEN src = 'CRN' THEN 1 ELSE 0 END) AS CreditNotes,
SUM(CASE WHEN src = 'INV' THEN Amt ELSE 0 END) AS GrossRevenue,
SUM(CASE WHEN src = 'CRN' THEN Amt ELSE 0 END) AS Returns
FROM (
SELECT T0.CardCode, T0.CardName, 'INV' AS src, (T1.DocTotal - T1.VatSum) AS Amt
FROM OCRD T0 INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode
WHERE T1.CANCELED = 'N' AND YEAR(T1.DocDate) = YEAR(GETDATE())
UNION ALL
SELECT T0.CardCode, T0.CardName, 'CRN' AS src, (T1.DocTotal - T1.VatSum) AS Amt
FROM OCRD T0 INNER JOIN ORIN T1 ON T0.CardCode = T1.CardCode
WHERE T1.CANCELED = 'N' AND YEAR(T1.DocDate) = YEAR(GETDATE())
) Data
GROUP BY CardCode, CardName
) Summary
WHERE Returns > 0
ORDER BY ReturnPct DESC| CardCode | CardName | Invoices | CreditNotes | GrossRevenue | Returns | NetRevenue | ReturnPct |
|---|---|---|---|---|---|---|---|
| C10089 | Widget Inc | 15 | 4 | 45000.00 | 8500.00 | 36500.00 | 18.9 |