Finding Most Recent Instance of Multiple Records*

[mjohns10]

*working title for now.

I'm trying to find instances outside an 8 month period. My trouble is, while returning the instances outside the 8 month period, I'm also returning some inside 8 months.

Say I have 3 clients who have had transactions over varying dates.

Purchases made
Client 1: 1/1/06, 1/1/08, 1/1/09
Client 2: 1/1/2006, 1/1/07, 1/1/08
Client 3: 1/1/09


Client 1 & 3 are no interest to me as they've both had at least one transaction within that 8 month period.

Client 3 is no problem, he doesn't show up on my report like I expect and want.

My two issues:
1) I'm having trouble getting my report to exclude Client 1 because of the 1/1/09 transaction because it also fits the criteria for having a transaction outside the 8 months.

2) While I'd like to only see Client 2 returned, I'd like to only display the most recent transaction on 1/1/08. I'm not interested in the 1/1/06.


Hope I've explained it well enough, but I can go into more detail if necessary. Hope one of you gurus can help! Thanks a lot.

M

 

[hilfy]

So, if I understand you correctly, you want all of the clients who have a transaction within an 8 month period who do not also have a transaction outside of that 8 month period, correct?

Depending on which version of Crystal you're using, the type of database and you're ability to write SQL, instead of using tables you could create a "Command" that has the SQL to pull all of the data for your report. It would have the basic form of:

Select <fields>
from <tables and joins>
where <date field> between <start date> and <end date>
and <other selection criteria>
and not exists
(Select 'X'
from <transaction table>
where client id = client id from main sql
and not (<date field> between <start date> and <end date>)

I think there also may be a way of doing this wil aliased tables, but I'm having a hard time seeing the conditions you need right now. I'll let it rumble around in my brain and see what I can come up with.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[mjohns10]

Dell,

Thanks for replying!

I am using Crystal XI and our DB is Oracle 10g.

>>>>
So, if I understand you correctly, you want all of the clients who have a transaction within an 8 month period who do not also have a transaction outside of that 8 month period, correct?
<<<<

Actually the opposite, but your logic seems as though it can be applied either way.

I'm going to find out what my ability is once I get a chance to start applying your ideas (!!). Thanks for the direction so far!


M

 

[lbass]

You neglected to say what the selected 8-month date range was for your example.

-LB

 

[mjohns10]

I'll be designing this to exclude tranactions within 8 months back from the current date, at any given point in time the report will be run.

Sorry! I was tyring to imply that, but wasn't clear.



I'm currrently working with a View we created on the DB. I'll report back on how it works.

 

[lbass]

Instead of record selection, use group selection (report->selection formula->GROUP). First insert a group on client (I'm assuming your data has the dates in a column rather in a comma-delimited string as shown), and then go to the group selection formula area and enter:

maximum({table.date},{table.client}) < dateadd("m",-8, currentdate) and
{table.date} = maximum({table.date},{table.client})

You would need to use running totals if you are doing calculations across clients, since non-group selected records would contribute to the more usual inserted summaries.

-LB