Home End User
New Blog Posts: Merging Reports - Part 1 and Part 2

Help on an unusual report

edited June 2003 in End User
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.

Comments

  • edited June 2003
    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.

  • edited June 2003
    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


  • edited June 2003
    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,

This discussion has been closed.