Skip to Content
author's profile photo Former Member
Former Member

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 22, 2015 at 06:22 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 22, 2015 at 06:16 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.