Help on an unusual report

rbuser

I have an unusual report request that I don't know how to approach -
any ideas? RB7.02 / D6 DADE with rap

One table with columns: ID, name, date. There are multiple rows for
each name but with different date values.

I need a report showing an ordered list, unique by name in ascending
date order where the date is the most recent date value for that name.
This is essentially a "whose turn is it next" report.

The kicker is that if there are two names with the same most recent
date then they must be ordered by their respective next most recent
date values and so on.

Example:

1, Sally, 22May01
2, Barney, 09May01
3, Fred, 09May01
4, Barney, 07May01
5,Ralph, 15Apr01
6, Fred, 07May01
7, Barney, 01May01
8, Fred, 03May01

Report should read:

5,Ralph, 15Apr01
2, Barney, 09May01 <== Barney must be before Fred
3, Fred, 09May01
1, Sally, 22May01

Cheers,


Rick Matthews
Dartek Systems Inc.

rbuser

Sorry, I can't help you with this one, but just for such difficult cases, in
my implementation of RB end-user can assign execution of StoredProcedures
right before report preview. This would be a solution for you, since such
things can be solved almost only with Stored Procedures.

digitalmetaphors

Sounds like you need to only print the names based on the last date when
there is a match on data between two names. In your example, the 07May01
records for Barney and Fred never print. In SQL, you would have to be able
to select distinct names with maximum date values, and order by the date,
whic is only the max dataes for each name. What happens when you select a
calculated field on max date and then add a where clause on this value? It
may not be supported on all databases. You may have to use a stored
procedure as Moisey suggests to preprocess the data.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Thanks Jim and Moisey,

I created a db user function to return an ordered string of
historical dates for a specific user that could them be used in an
order clause in RB.

Thanks,