Sales by customer for a date range

Net sales per customer (invoices minus credit notes) between two dates, highest first.

Tables: OINV, ORINUse: Monthly sales meetings, customer ranking, commission checks.Parameters: [%0] = from date, [%1] = to date

SELECT X.CardCode, X.CardName, SUM(X.NetSales) AS NetSales
FROM (
  SELECT T0.CardCode, T0.CardName, T0.DocTotal - T0.VatSum AS NetSales
  FROM OINV T0
  WHERE T0.CANCELED = 'N' AND T0.DocDate BETWEEN '[%0]' AND '[%1]'
  UNION ALL
  SELECT T1.CardCode, T1.CardName, -(T1.DocTotal - T1.VatSum)
  FROM ORIN T1
  WHERE T1.CANCELED = 'N' AND T1.DocDate BETWEEN '[%0]' AND '[%1]'
) X
GROUP BY X.CardCode, X.CardName
ORDER BY NetSales DESC

SELECT X."CardCode", X."CardName", SUM(X."NetSales") AS "NetSales"
FROM (
  SELECT T0."CardCode", T0."CardName", T0."DocTotal" - T0."VatSum" AS "NetSales"
  FROM OINV T0
  WHERE T0."CANCELED" = 'N' AND T0."DocDate" BETWEEN '[%0]' AND '[%1]'
  UNION ALL
  SELECT T1."CardCode", T1."CardName", -(T1."DocTotal" - T1."VatSum")
  FROM ORIN T1
  WHERE T1."CANCELED" = 'N' AND T1."DocDate" BETWEEN '[%0]' AND '[%1]'
) X
GROUP BY X."CardCode", X."CardName"
ORDER BY "NetSales" DESC

Need your UDFs in this report, or a dashboard? We extend it for your company.Customize it →

Sales

More sales queries

Top 20 selling itemsBest-selling items by sales value, with quantity sold, for a date range.Sales by sales employeeInvoice count and net sales per sales employee for a period.Monthly sales trendNet invoiced sales per month for the last 12 months.Open sales orders with open quantityEvery open sales order line that still has quantity to deliver.

How it works

Three steps, no signup

Copy the right version

Use the SQL Server tab for MS SQL databases and the HANA tab for SAP B1 on HANA.

Open Query Generator

In SAP Business One go to Tools → Queries → Query Generator, paste and click Execute.

Save and share

Click Save, pick a category and give the query a name. Your team can run it from Query Manager.

FAQ

Questions about this tool

How do I run the sales by customer for a date range query in SAP Business One?

Copy the version that matches your database, open Tools → Queries → Query Generator, paste it in the SQL box and click Execute. Save it to reuse it later.

Which tables does this query use?

It reads OINV, ORIN from the standard SAP Business One schema. No user-defined fields are needed.

What should I enter when the selection window opens?

This query uses parameters: [%0] = from date, [%1] = to date. SAP B1 shows a selection window for each one when you run it.

Can this be turned into a dashboard or alert?

Yes. We turn queries into Crystal reports, Excel reports that refresh themselves, SAP B1 alerts or WhatsApp messages for managers.

More free tools

Built for SAP Business One teams

SQL to HANA query converterPaste a SQL Server query and get the SAP HANA version, with every change explained.SAP B1 table finderSearch any table: what it stores, its line tables and its key fields.ZATCA QR code decoderRead a Saudi e-invoice QR (TLV Base64) or generate a Phase 1 QR from invoice values.SRI clave de accesoValidate or generate the 49-digit access key of Ecuador electronic invoices.

Need this inside SAP Business One?

We build add-ons, reports and integrations for SAP B1 on SQL Server and HANA. Tell us what you need and get a fixed price, usually within 24 hours.

Start a project Chat on WhatsApp