Filter most recent record in group

[ecsrs]

Crystal Reports XI
SQL database

I am trying to filter the most recent score based on a group. My table has customerid, scoretype, score, date created. I have grouped on customerid and under selection formulas - group I added the following formula:

{DATECREATED} = Maximum ({DATECREATED}, {CUSTOMERID})
and
{SCORETYPE} = 4142

I am running into 2 problems. If there isn't a record for a customer for the score type I am looking for then I need to display a zero for the score. The other problem I have is I need to group on the score result to sort customers by highest to lowest score. When I group on the score result currently; it displays all scores for a particular client instead of just the most recent score. Any suggestion would be greatly appreciated.

 

[lbass]

There are some inconsistencies in what you are describing. It is unclear whether you only want:

1) the most recent record IF the scoretype = 4142 or
2) the most recent record of those where the scoretype = 4142.

Also, if you want to select one of those, then you wouldn't see a record where the scoretype <> 4142. So, I think you should provide a sample of data that shows the possible variations in dates/scoretypes and scores, and then show us what you would want to see displayed for that sample.

-LB

 

[ecsrs]

I am looking for the the most recent score where the score type = 4142. However I want to show all customers even if they do not have a record for scoretype = 4142. In that case I need to display a score of zero.

Here is sample data:
custid datecreated scoretype score
15146 10/1/2008 2:52:20PM 4140
15146 12/10/2008 12:56:59PM 4142 19
15146 7/29/2008 3:59:26PM 4139
71503 12/23/2008 2:08:40PM 4138
71503 12/10/2008 3:48:28PM 4142 18
71503 12/22/2008 3:53:28PM 4142 20
79721 12/18/2007 9:33:41AM 4138

For ID 15146 I want to show 19 as the score and ignore the other types. For ID 71503 there are two scores for scoretype 4142 but I want the most recent score of 20. For ID 79721 there isn't a record for scoretype 4142 but I want to display zero.

In the end I need to group by the score so I can sort customers by their score. This is where I am running into problems. When I group by score it shows all client scores = 4142 not just the most recent.

 

[lbass]

Go into the field explorer->SQL expression and create {%maxdt4142}:

(
Select max(`datecreated`)
from table A
where A.`scoretype` = 4142 and
A.`custid` = table.`custid`
)

The punctuation depends upon your datasource. If you are unsure, go to database->show SQL query and observe the punctuation there. I was unsure whether scoretype was a number or a string. Adjust this accordingly.

Then in the main report, create a formula {@mostrec4142score}:

if isnull({%maxdt4142}) then
0 else
if {table.datecreated} = {%maxdt4142} then
{table.score}

Place this in the detail section and insert a maximum on it at the custid group level. Drag the result into the custid group header. Then go to report->group sort and select 'All', maximum of {@mostrec4142score}, descending. Suppress the detail section and group footer.

-LB

 

[ecsrs]

Thanks so much. It worked perfectly. Tried something similar before writing the post but must have had something incorrect.