SAP B1 Query to show a list of customers who have not bought anything in 12 months.

[former_member603219]

Hello

can you please show me a query to show customers who have not bought anything in the last 12 months.

Customer Code, Customer Name, Total Sales - Total Credits

Thank you.

[Johan_H]

Hi Stephen,

If customers have not bought anything in the last 12 months, Total Sales will ipso factum always be zero. As Crediting (usually) happens after the fact, it would not be related to the sales that did not happen in the last 12 months. So I will leave those columns out, but with this query you will get a list of (active) customers who have not ordered anything in the last year:

SELECT c.CardCode
      ,c.CardName
FROM OCRD c
WHERE c.CardType = 'C'
  AND c.frozenFor = 'N'
  AND c.CardCode NOT IN (select distinct CardCode
                         from ORDR
                         where DocDate > dateadd(year, -1, getdate()))

If with Total Sales and Credit you had something else in mind, I recommend that you look into learning sql (it is free, and it is not as difficult as it may seem), so you can adapt this query yourself, and will be able to create these kinds of reports yourself. I say this, because we want to help you with any problems and challenges you may have, but we do not get paid for it. That means that generally it is assumed that you make an effort as well.

Regards,

Johan

[former_member603219]

thank you Johan. I am trying to do this myself but I do not have much computer language background. sometimes we just need some sort of reports urgently.

this is my code and I think I have got what I need:

SELECT distinct T0.[CardCode], t0.CardName FROM OINV T0 inner join OCRD T1 on t0.CardCode = t1.CardCode
where T1.[frozenFor] = 'N' and t1.CardType = 'C' and t0.CardCode not in (select distinct t0.CardCode from OINV T0 where T0.DocDate > dateadd(day,-365,getdate()))

[Johan_H]

Hi Stephen,

That is a nice adaptation of the query I provided. Now you can adjust this query to show you the same effective data for any amount of time you may want, a week , a month, etc. B1's query generator is a perfect tool to learn sql, and many a user on this forum has learned it this way.

On an analytical note, please note that by using the OINV table (sales invoices), the report now shows you the customers that you have not sent an invoice to. That means that the list may include customers that have ordered from you, but goods/services have simply not been delivered yet.

If your query is answered, please close this thread by clicking the accept link under the answer that best answered your question. This will benefit other users with the same question.

Regards,

Johan