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

[Former Member]

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

[Former Member · Accepted solution]

Hi Stacy,

Please try below query.

Hope this helps you.

SELECT T0.[CardCode] AS 'BP Code', T0.[CardName] AS 'BP Name', 
(Select Max (OINV.DocDate) From OINV Where OINV.CardCode=T0.CardCode) 'Latest Invoice Date'
 FROM [dbo].[OCRD] T0 
Where T0.[frozenFor] = (N'N') AND T0.[validTo] IS NULL AND T0.[CardCode] NOT Like '%-%'

Thanks,

Rahul

[Former Member · Accepted solution]

You guys are very close.   We set up customers like this

c1007  Construction Company#1  Invoice Date 10/1/2015

c1007-1  Construction Company#1 first job Invoice Date 10/2/15

c1007-2  Construction Company#2 second job Invoice Date 9/30/2015

So  I need the most recent document date for c1007 and his jobs (consolidated BP)  which in the above case would be from BP c1007-1 10/2/15

So I need to change the portion of the query "WHERE CardCode = TO.CardCode to a LIKE statement, but I am failing to get the exact syntax of the LIKE statement

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Thank you, I have run the query and verified the results, I got exactly what I was looking for!

[Former Member · Accepted solution]

Hi Stacy,

Please make sure that the results are correct for other suppliers as well.

Thanks,

Rahul

[Former Member · Accepted solution]

I did verify and it does work for all suppliers/customers.

[frank_wang6]

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

(SELECT TOP 1 DocDate FROM OINV WHERE CardCode = T0.CardCode AND CANCELED = 'N' ORDER BY DocDate)

FROM [dbo].[OCRD] T0 Where T0.[frozenFor] = (N'N') AND T0.[validTo] IS NULL