I am running Crystal Reports 8.5 wi

[Randygk]

I am running Crystal Reports 8.5 with a SQL 2000 database. I am trying to return the date of the last order for each customer id number and then compare it to current date and show it only if it is older than 180 days.

order_no order_date customer_id
102005 12/24/2002 10002584
105001 9/25/2003 10540001
106012 12/21/2003 10500142
106255 8/27/2003 10600045
103298 1/15/2003 10540001
104125 6/18/2003 10500142
103546 4/28/2003 10611223

data retrieved
customer_id order_date order_no
10002584 12/24/2002 102005
10611223 4/28/2003 103546

Thank You in advance
Randy

 

[synapsevampire]

Group the report by CustID

In the report->record selection formula->group place something like:

maximum({table.date},{custid}) < currentdate-180

That should work (sorry, don't have CR here so I can't test).

-k

 

[Randygk]

I tried that when I save the selection criteria I get an error &quot;The summary / Running total field could not be created.

 

[lbass]

You have to group on {table.custID} before doing the group select. Also make sure you are putting SV's formula in report->edit selection formula->GROUP.

-LB

 

[Randygk]

That looks like it is working I had the wrong table for the cust_id

Thanks all for your help

 

[synapsevampire]

Thanks for the verification, LB, I'm working on a pure SQL contract right now and can't test CR.

-k