Maximum Date as Record Selection

[crogers111]

I've seen a couple posts on this issue and have tried to implement the recommendations for my scenario but have been unsuccussful.

CR 10
OLE DB-SQL

I have a RT formula at the grouping level that includes records where the datestamp falls within an inputted date parameter (November in this case). The RT resets for each group {Stage}

I have a RT formula in the report footer that also includes records where the datestamp falls within an inputted date parameter (November in this case) doesn't reset. i.e. should be a total of all groups

Each are a distinct count of PersonID.

The Report has a record selection to select records with a status of CLOSED

Due to a user error and users needing to correct erroneous entries I have duplicate entries for a participant but only want to show and count the maximum date.


Here's THE CURRENT OUTPUT and problem:

RH CLOSED CASES
GP 1 Withdrew 2
GP 1 Inelgible 1

RF Subtotal 2 <---distinct count of personID for Nov

HERE'S THE DESIRED OUTPUT:

RH CLOSED CASES
GP 1 Withdrew 1
GP 1 Inelgible 1

RF Subtotal 2 <---distinct count of personID


Here's what the details of the data look like

WITHDREW
PersonID DateStamp Status Stage
1274 02/12/2004 4:1236 Closed Withdrew
1121 11/17/2004 5:2812 Closed Withdrew
2331 11/04/2004 3:4524 Closed Withdrew
Subtotal for Withdrew 2 (<--- RT distinct count of personID for November)

INELIGIBLE
PersonID DateStamp Status Stage
1121 11/17/2004 5:2951 Closed Inelgible


from a user perspective: on 11/17, user mistaknly saved data for PersonID 1121 with stage of Withdrew but then went back immediately (see date/time stamp) and saved again with correct stage, ineligible..but both records are now in the database.

I want only the Maximum Date for a given personID within the date range of November.

 

[lbass]

Because your grouping will force multiple cases to appear per person ID if there are multiple statuses for that person, I think you need to take care of this with a SQL expression {%maxdate}:

(select max(AKA.`datestamp`) from Table AKA where
AKA.`PersonID` = Table.`PersonID`)

Substitute your table name for "Table" wherever it appears in the formula, and substitute the exact field names for `datestamp` and `PersonID`. Leave "AKA" as is, since it is an alias table name. Then go to report->edit selection formula->record and enter:

{Table.datestamp} = {%maxdate}

Now the customer should appear only once in the report, with his/her most recent date.

-LB

 

[crogers111]

That did it !!

Thanks for the help.

C-