Displaying Max value row in groups...

[bgravi]

Hi,
I am trying to do the following:

Please consider this scenario. One table has list of all customers, and another table has all the purchase made by this customers. The two tables are linked by customer id.
Now, I need to present a report listing the most recent purchases of the customers in this format.

Last 30 days-
list of all customers whose last purchase was in this time frame.
Last 30-60 days
List of all customers whose last purchase was in this time frame.

etc...

I need to list only one record per customer( which is the most recent purchase).

How can I do this? I am having difficulty in getting the report to display only the latest purchase. It lists all the purchases done.

Thank you
Ravi

 

[lyanch]

You need to edit your SQL statement to only bring back the last day.

In the where section add:

purchase.date = (select max(p.date) from purchase p where
p.customer_id = customer.customer_id)

where the customer.customer_id is in from your main query.

Then you will only have the "last purchase" returned. You may need to play with it a bit to get the syntax right for your DB.

Lisa

 

[bgravi]

I am using Crystal Reports for Visual Studio.Net and there isn't any provision for adding the Where statement!!!