Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Query help for a listing of all active customers with last invoice date

I have one query that gives me a list of all active customers and one query that gives me the last invoice date.  I would like to see them combined into one query or one crystal report.

Query 1:

SELECT T0.[CardCode] AS 'BP Code', T0.[CardName] AS 'BP Name' FROM [dbo].[OCRD] TO Where T0.[frozenFor] = (N'N') AND T0.[validTo] IS NULL AND T0.[CardCode] NOT Like '%-%'

Query 2:

Select Top 1

T0.[DocDate] AS 'Last Invoice Date', T0.[CardCode] Like n'%[%0%' Order by T0.[DocDate] DESC

Thank you!

Stacy

Tags:
replied

Hi Stacy,

I am not sure but please try this...

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

(Select Max (OINV.DocDate) From OINV Where SUBSTRING(OINV.CardCode, 1, 5)=T0.CardCode) 'Latest Invoice Date'

FROM [dbo].[OCRD] T0

Where T0.[frozenFor] = (N'N') AND T0.[validTo] IS NULL AND T0.[CardCode] NOT Like '%-%'

If the length of the Card Code is not consistent in your DB then this query may give wrong result for other suppliers.

Thanks,

Rahul

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question