cancel
Showing results forΒ 
Search instead forΒ 
Did you mean:Β 

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

deedee1
Explorer
0 Kudos

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 πŸ™‚

Accepted Solutions (1)

Accepted Solutions (1)

jitin_chawla
Advisor
Advisor

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
Active Contributor

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
Explorer

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
Explorer

Thank you so much, Mr Johan for your input.

I got the hang of this. πŸ™‚

Answers (0)