Select current data from history table

[Vittles]

I am working with a oracle server/multiple table database and Crystal Reports version 7.

One of the tables I am working with is for clients (demographic info), one is for case history(open date, reason...), and one is for client information connected to specific cases (involvement dates...).

I am trying to pull the clients who are not active in any case, but am only getting results based on the specific cases the client isn't active in. For example, John Smith may have a case that is closed with an involvment span that is enddated, but may also be actively involved in a new case that is still open. The report is only pulling his old involvement/case info, not that he is still involved in a new case.

Is there a way to pull only the most recent involvement span (involvement span end date field is a history field that is under the client-case combined table) and specify to only pull it, if the end date of that current span is not null? Basically only pull clients who are not active in any case no matter how many they have been involved in...

I am grouping this report by client ID so that the information should be populated based off of any case history.

Any ideas would be great.

 

[synapsevampire]

Can you provide example data and expected output?

You should be able to filter the rows based on a field being null.

But this is a little fuzzy for me right now.

-k

 

[lbass]

You might try grouping on the client ID and then going to report->edit selection formula->group and entering something like this:

{table.involvement_span_end_date} = maximum({table.involvement_span_end_date},{table.clientID}) and
{table.involvement_span_end_date} > maximum({table.opendate}, {table.clientID})

I think we need more info about your table structure, but maybe this will help you on your way. Substitute whatever field identifies the startdate of a new case for {table.opendate}. I would test the results carefully, since working with history tables can be complicated.

-LB

 

[Vittles]

Sorry I should have given more details - maybe this will be enough...

Table Name: Fields
Case: Case ID, Case open date, Case enddate
Client: Client ID, First Name, Last Name, Demo fields
Case-Client:Case ID, Client ID, Involvement start date, Involvement End date

For each client there may be multiple cases and thus within each case the client may have multiple involvement start through end dates.

John Smith is in case ID 12334 and Case ID 12456
His involvement in case 12334 was from 1/02/2002-1/15/2003.
His involvement is case 12456 is from 2/15/2003-(no end date).

Jane Doe is in Case ID 5678 and Case 89706
Her involvement in both cases is endated.

I need my report to only show Jane Doe's name, her case ID and involvement spans since she is closed in all of them. John Smith shouldn't show up at all as he is open in case 12456.

My report is currently still showing John Smith because his involvement end date is filled in one case - the formula doesn't check through all of his enddates & then not show him since he is open in one - that's the piece I am missing.
(very basic) formula =Not IsNull({Case_Client.Involvement_EndDate})

Somehow I need to have the system go through all the involvement spans for each client/case & only show the ones that have no open involvement spans (all enddates in all cases filled)

Hope this helps. Thanks for any input!

 

[lbass]

I think my formula should work--have you tried it? Just substitute {case-client.involvementstartdate} for {table.opendate} and {client.clientID}, which should be your group field}. The formula will give you all clients and all of there corresponding information as long as the most recent closed date is greater than the most recent open date, i.e., there are no open cases.

If for some reason it doesn't work, please show the results you get.

I don't think using "not isnull(client_involvement.enddate}" will work as a group select, since anyone who has at least one enddate will be selected, but they could still have an open case.

-LB

 

[Vittles]

I tried working with the Maximum(start date field) formula & the formula check rejected it saying it needed to have boulean results. Will try some more on Monday - maybe I just need a fresh start.

Thanks

 

[lbass]

Make sure you are placing this in the group select, not the record select. Try copying and pasting my formula and then substituting the actual field names.

-LB