Find customer who has no Sales Order from last two months?

[deedee1]

Greetings,

I have been trying to find customers who has no sales in the past two months. I'm basing it on the fact that they have had no product delivered in the past two months. But, how can I exactly get the name of those people?

I've tried:

NOT IN, EXCEPT and MAX aggregate function but nothing worked.

I'm new to SAP B1 and HANA and can't figure out any solution for a week. I'd really appreciate some help. Thanks 🙂

[jitin_chawla · Accepted solution]

Hi,

Check below:

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 DAYS_BETWEEN ("DocDate", CURRENT_DATE) < 60)

Adapted from query provided by johan.hakkesteegt at the below link:

https://answers.sap.com/questions/12635951/sap-b1-query-to-show-a-list-of-customers-who-have-.html

If the above is not helpful, will wait for expert inputs from Johan on it.

Kr,

Jitin

[Johan_H · Accepted solution]

Hi,

This query will show customers who have not had Sales Orders entered for them in the last 60 days.

To show customers who have not had anything delivered to them change ORDR to ODLN

To show customers who have not had any invoices entered for them change ORDR to OINV

Regards,

Johan

[deedee1 · Accepted solution]

Thank you so much Mr./Ms. Jitin Chawla for your input.

This is exactly what i was looking for. I can't tell you how much in stress i was.

I'd love to treat you to a coffee. I owe you a big one. Please let me know if i can do anything for you. 🙂

[deedee1 · Accepted solution]

Thank you so much, Mr Johan for your input.

I got the hang of this. 🙂