What I'm looking for is a list of Cases and want to include the date of the most recent Event completed (if applicable--in some cases there won't be an event associated with it). Furthermore, the "Event" I'm interested in displaying is for a particular type only (called "Event-Eval" in the example below).
I want to stay away from Grouping because we use direct list editing on the results... and grouping causes us to click away from the list too frequently. This is a highly interactive results list and direct list editing is essential.
I currently receive a list that has multiple lines for the same Case because it returns a separate line item for every Event on the customer's account. The results currently look like this:
COMPANY CASE # ACTIVITY ACTIVITY COMPLETED DATE
ABC Co. Case #1234 Task 5/12/08
ABC Co. Case #1234 Event-Demo 5/10/08
ABC Co. Case #1234 Event-Eval 4/10/08
ABC Co. Case #1234 Event-Eval 2/10/08
ABC Co. Case #1234 Task 3/15/08
555 Inc. Case #6431 (no activity)
XYZ Co. Case #567 Event-Eval 4/15/08
123 Co. Case #3210 Task 3/01/08
123 Co. Case #3210 Event-Eval 2/17/08
123 Co. Case #3210 Event-Demo 1/17/08
What I want is a list to return a single line item for each Case and the date of the most recent Event-Eval (if one exists)... so it would look like:
COMPANY CASE # ACTIVITY TYPE ACTIVITY COMPLETED DATE
ABC Co. Case #1234 Event-Eval 4/10/08
555 Inc. Case #6431
XYZ Co. Case #567 Event-Eval 4/15/08
123 Co. Case #3210 Event-Eval 2/17/08
I use the following to display a date for the "Event-Eval" records only:
DECODE({activity.custeventr7walkthru},'T',{activity.completeddate},'')
I think I need to add the DENSE_RANK command in there somehow to get just the single result (for the most recent completed Event-Eval)... but I'm not sure how to incorporate it.
Any ideas?
Thanks!
Derek