show latest date value along with field value in group footer level

[howaboutthisone]

Hope this isn't to simplistic for everyone, but I'm not strong in SQL.

Ok here's my situation. I work for a healthcare company and so I need to build a report that captures the latest date value for a specific lab test result and places those results at the group footer level.

For instance, at the detail line item level you have multiple dates per patient

Name Test Date Test Result
John Doe 1/1/08 50
John Doe 1/30/08
John Doe 2/13/08 114
John Doe 3/1/08
John Doe 3/15/08
John Doe 6/12/08

At the group footer level I'd like to display this
John Doe 2/13/08 114

So at the group footer level I already have it grouped by patient, but I want to display the latest test date along with its results that are populated. In this case, it would be 2/13/08 for the test date and also display the 114 test result, displaying them at the group footer level.

 

[blom0344]

You do not state where you are working with (WebI, Desktop intelligence?) and with what database, but if you want it solved with SQL, then you could use a rank construction:

Rank() over (Partition BY Name order by Testdate,TestResult DESC)

which would yield:

2 John Doe 1/1/08 50
6 John Doe 1/30/08
1 John Doe 2/13/08 114
5 John Doe 3/1/08
4 John Doe 3/15/08
3 John Doe 6/12/08

Obviously the row with the rank 1 meets the demand.
Rank() is supported by Oracle 8 and higher , SQL server 2005, DB2 7 and higher.
Depending on the tool you may need a second query though..


Ties Blom

 

[howaboutthisone]

Oops... sorry about that. I'm using Business Objects (Crystal Reports v10) that's linked to a Microsoft SQL server with relational database tables.

 

[howaboutthisone]

I'm toying with variable declerations (date variable & number variable) but not sure if I'm wasting my time

 

[blom0344]

I have zero experience with Crystal Reports, but if you are on SQL server 2005 , then you could use rank in your SQL.
Does Crystal Reports use universe as a metadata layer? If so ,then you can define a dimension that holds the code I gave you..

Ties Blom