Useful SAP Business One Queries: When Did Your Customers Last Purchase from You?

Richard Duffy
Richard Duffy in SAP Business One, SQL Queries January 25, 2018

Part two of our series on useful SAP Business One queries. If you missed part one, sales orders with multiple deliveries, you can access it here.

Today's focus is on query that can help you identify customers that haven't purchased from you in a certain amount of time.

The Business Case

A lot of insight is to be gained from better understanding your customers. For this query, seeing all of your customers, along with the date when they last purchased from you, could set up a number of ensuing actions.

Perhaps you want to focus on those customers who have gone the longest without a recent purchase. You may want to run a marketing campaign to reactivate them, or potentially schedule follow-up calls with your sales reps. 

Or perhaps you want to know which customers have purchased from you most recently. That could set up a customer service survey, special promotion, or just help you better understand your recent customer pool. 

You can use the query below as the starting point, and with a couple of additional fields, it can fit all the scenarios I mentioned.

The SQL Query code

SELECT T0.[CardCode] AS 'BP Code', T0.[CardName] AS 'BP Name',

(Select Max (OINV.DocDate) From OINV ) 'Latest Invoice Date'

FROM [dbo].[OCRD] T0 WHERE T0.[CardType] = 'C'

Refine the Data with Filters and Additional Fields

As intended, this query pulls all the business partners who are flagged as customers.

You can add the T0.[SlpCode] in the SELECT statement field and filter on it to identify a specific sales person's customers like this—the [%0] will prompt you for a sales person code value at run time.

SELECT T0.[CardCode] AS 'BP Code', T0.[CardName] AS 'BP Name',

(Select Max (OINV.DocDate) From OINV ) 'Latest Invoice Date'

FROM [dbo].[OCRD] T0 WHERE T0.[CardType] = 'C' AND T0.[SlpCode] =[%0]

Useful Queries for SAP Business One

As mentioned in the first blog in this series , the discussion of useful queries for your SAP Business One solution is our most popular post, and there are more queries to review throughout the thread.

Over the years, however, the thread has become a bit bogged-down with many comments and questions about other queries. As previously, the original author sometimes appears to have moved on to other priorities; however, you have ONE.Source (and me) as a resource for both queries and other topics.  So I encourage you to ask your questions via the Q&A in our community forum or share your favorite query by starting a new discussion thread. Who knows, perhaps your post will be our most popular in a few years' time. 

Ask a Question or Start a New Discussion