Record with latest timestamp

[JacobsAunt]

I am having a dilemma. I am using CR 8.5. I have a DB that does not support stored procedures. I need to know how I can find the last interaction to a customer issue. The structure is such that I have an issue (Case) that may have multiple interactions (solutions). I need to be able to find the last solution based on timestamp to determine case age and status. How can I get Crystal to show me the solution with the last timestamp without using stored procedures?

 

[cmpgeek]

when you say stored procedures - is that the same as transaction tracking features? we have transaction tables that we can go into and see the time/date stamps of each entry - who made it and from what PC... ShannonLea

 

[JacobsAunt]

It seems that I was not very clear. My issue is not one of the data being physically present. What I need to do is a one to many type join that enables me to list all solutions for a case, then do some calculations and find a status based on the last solution timestamp matched against the case timestamp. I need to be able to do this without using functions called stored procedures

 

[lyanch]

Add a subquery to your SQL in the where statement (database->showsqlquery):

table.date = (max(t.date) from table t where t.customer = table.customer)

Lisa

 

[JacobsAunt]

I am sorry, My DB does not support SubQueries either. I am looking for a Crystal solution if it exists.

 

[lyanch]

group by customer, sort by date and place all your items in the group header and suppress the details.

Lisa

 

[JacobsAunt]

I tried that the first round. I have some calculations that made this awkward and inconsistent. What I ended up doing was a series of formulas that took the maximum date from the details section, then matching ID numbers against that max date and performing various calculations from various fields based on the ID. I guess I just gave up too soon. I have the report working now. Thanks for your help.

 

[Naith]

Your report would be more efficient if you grouped by customer, and used a group selection criteria of:

{Date} = Maximum({Date},{GroupField})

Naith

 

[JacobsAunt]

Naith, I am having trouble with this suggestion. Could you explain further? I can create the formula, but I am not sure what you mean by Group Selection Formula. I cannot use the formula I have created. (It is not available for grouping) I guess I am a little slow this morning.

 

[Naith]

No problem.

This is how you create a Group Selection criteria:

Assuming you are grouping by Customer, go to the Report menu, Edit Selection Criteria, and select Group from the sub menu.

In the editor window, enter:

{YourTimeStampField} = Maximum({YourTimeStampField},{YourCustomerField})

obviously replacing the names of the fields in this example with your relevant database fields.

Naith

 

[JacobsAunt]

Thanks. I have another question for you. How does this make the report more efficient? I can see that I reduce the number of records in each group, but I am not sure what the benefit of this is.

 

[Naith]

You process and return a smaller number of records.

When you suppress your excess rows, you're wasting X% of the rows which are being returned to you by the database. The idea is to return only the rows which you need, leaving you with 0% (or as close to 0% you can get) wastage.

This means a faster report.