View most recent data after group by function

[subendu]

I have 2 tables as listed below

1. Activity table

col type
--- -----
oid number(PK)
create_time datetime
oppr_oid number(FK)

2. Opportunity table
col type
--- -----
oppr_oid number(PK)
opportunity_id text

Values for the above tables can be

Opportunity
1 Opp1
2 Opp2
3 Opp3
4 Opp4

Activity
1 04/01/04 1
2 04/03/04 2
3 04/03/04 1
4 04/11/04 3

I want to generate a report based on the below query
Also the report should show data on weekly basis(group by createtime).

select opportunity.opportunity_id,max(activity.createtime) from opportunity,activity where opportunity.oppr_oid=activity.oppr_oid group by opportunity.opportunity_id

The report should look something like as shown below

Week 04/01/04
Opp1 04/03/04
Opp2 04/03/04

Week 04/08/04
Opp3 04/11/04


I have tried using group by Opportunity_id(ascending) and put createtime and opportunity_id in the group footer. Also I suppressed the header and detail section. But in that case I am not able to display the data in weekly basis.

Any help will be highly appreciated.

 

[synapsevampire]

The group by was correct, but just place the date field in th details, right click it and select Insert->Summary->Maximum

Now select Database->Perform Grouping on Server.

Right click the details and select Suppress

Check the Database Show SQL Query to make sure that the Grouping is being done by the database (you shouldn't have any other fields on the report).

The above is Crystal and database dependent, which you should provide when requesting technical information.

-k

 

[lbass]

Try adding a subselect into the where clause in database->show SQL query, something like:

SELECT
opportunity.`opportunity_id`,
activity.`createtime`
FROM
`opportunity` opportunity INNER JOIN `activity` activity ON opportunity.`oppr_oid` = activity.`oppr_oid`
WHERE
activity.`createtime` = (SELECT MAX(AKA."createtime") FROM
activity AKA where AKA."oppr_oid" = activity."oppr_oid")
ORDER BY
opportunity.`opportunity_id` ASC

Then insert a group on the date field on change of week. By using this method, you will only return one record per opportunity ID even when you insert the week group. Otherwise, inserting the group could result in multiple values being returned per opportunity ID, since the maximum per ID would be evaluated within the week group.

I would save the report under another name before trying this.

-LB