cancel
Showing results for 
Search instead for 
Did you mean: 

Query help

Former Member
0 Kudos

Dear experts,

I need to create query for ,

Customer who has not spent at all in the last year.

Please assist,

Thank you,

Accepted Solutions (1)

Accepted Solutions (1)

former_member188586
Active Contributor
0 Kudos

hi

CHECK THIS ONE

SELECT  DISTINCT  T0.[CardCode], T0.[CardName]

FROM OINV T0 WHERE T0.[CardCode] not in (select T0.[CardCode] from OINV T0  where  T0.[DocDate] > =[%0] AND T0.[DocDate] <=[%1])

AND UPDATE THE STATUS

Former Member
0 Kudos

Hi,

Thanks for above query,

Its not giving me correct result it display all list of customers not according to date range given.

Answers (6)

Answers (6)

former_member188586
Active Contributor
0 Kudos

hi SANTOSH

AS I SENT query is working for me ,, tell me what exactly your Requirement...

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this query:

SELECT T0.[CardCode], T0.[CardName] FROM OCRD T0 WHERE T0.[CardCode]  not in (select  t0.cardcode from OINV t0 where datediff(yy,t0.docdate,getdate()) =1) and  T0.[CardType] = 'c'

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

HI sir,

Thank you for above query,

Can you Please change above query date function to normal date range?

Thank you.

kothandaraman_nagarajan
Active Contributor
0 Kudos

Try this:

SELECT T0.[CardCode], T0.[CardName] FROM OCRD T0 WHERE T0.[CardCode]  not in (select  t0.cardcode from OINV t0 where t0.docdate between [%0] and [%1]) and  T0.[CardType] = 'c'

Former Member
0 Kudos

Dear Sir,

I got below error when i run above query,

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Please check Mr.Ramudu query.

Former Member
0 Kudos

Try:

SELECT T0.[CardCode], T0.[CardName]

FROM OCRD T0

WHERE T0.[CardType] = 'c' and

T0.[CardCode] not in (select  cardcode from OINV where docdate between [%0] and [%1])

frank_wang6
Active Contributor
0 Kudos

man, obviously, you missed the closed bracket ) at the end.

Former Member
0 Kudos

How did you get such conclusion?!

frank_wang6
Active Contributor
0 Kudos

ha, my bad, didnt pay enough attention.

vicky_williams
Participant
0 Kudos

Or you can use "Inactive customer" report (Standard in SAP b1)

Business Partners >> Business Partner reports >> Inactive Customers

Fill in date from to get report for customers who have not spent since this date

When the report is opened it will have choice of tickboxes at the top (Quotations / Orders / invoices / Delivery notes etc.)

You can untick all apart from AR invoice and then hit refresh at the bottom of the screen

Former Member
0 Kudos

Hi,

Try:

SELECT T0.[CardCode], T0.[CardName]

FROM OCRD T0

WHERE T0.[CardType] = 'c' and

T0.[CardCode] not in (select  cardcode from OINV where datediff(yy,docdate,getdate()) =1) 

Thanks,

Gordon

former_member184146
Active Contributor
0 Kudos

Hi,

Try this

select distinct  cardcode

from oinv

where CardCode not in (select a.cardcode from oinv a where a.[DocDate]  > =[%0] and a.[DocDate] <=[%1])

former_member188586
Active Contributor
0 Kudos

Hi santosh

check once bellow Document and analyze

http://scn.sap.com/docs/DOC-59629

and Give me Clear Requirement

Thanks&regards

Andakondaramudu

Former Member
0 Kudos

Hi AndakondaRamdu,

Thanks for the above link.

I want Customer names who have not done any AR Invoices in last year.

Thank you,