Show record if Max Date with Criteria

[ecugrad]

Having difficulty formulating a report that pulls in data for the Account Number,if the dispostion field is equal to "s"

Problem, the table contains multiple records with the same Account Number on different dates, the dispostion field can contain A, R, C, E Or S. I just want to return the record if the Max Date for that Account Number is equal to "s".

Example:
Don't want anything returned
AccountNum Date User Dispostion
12345698 5/10/05 88 R
12345698 7/10/05 87 S
12345698 6/14/06 87 R


Would want the 6/14/06 record returned
AccountNum Date User Dispostion
12345698 5/10/05 88 S
12345698 7/10/05 87 R
12345698 6/14/06 87 S

Thanks for any help..

Mike

 

[Turkbear]

Hi,
Some details needed:

What version Of CR?

What Database?

What conenction method?

I suspect that a combination of a Command Object and a Table ( or a more complex Command Object/View/Stored proc) may be what is needed, but all that is very version-specific..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Try Grouping by the account number, and then in the Report->Select Formula->Group place:

(
{table.date} = maximum({table.date},{table.account})
)

Display the records in the group footer and suppress the other sections, and then right click the group footer and in the X2 next to suppress place:

{table.disposition} <> "S"

-k

 

[ecugrad]

I'm still returning records that the updatedate is not the maximum Updatedate for the account number. I have placed:

{BKRPTWOBLOG.UPDATEDDATE}=Maximum({BKRPTWOBLOG.UPDATEDDATE},{BKRPTWOBLOG.ACCOUNTNO}) in the Report-Select Formula-Group and added
{PROCESSLOG.DISPOSTION} <> "S" in the group footer and surpressed the other sections .

Anything that I'm missing? I'm using CR 8 and the date/time fieLd is Oracle in the format of 1/01/2006 3:12:59PM

 

[lbass]

I think you can just use a group selection formula of:

{BKRPTWOBLOG.UPDATEDDATE}=Maximum({BKRPTWOBLOG.UPDATEDDATE},{BKRPTWOBLOG.ACCOUNTNO}) and {PROCESSLOG.DISPOSTION} = "S"

-LB