Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

New_Value Associated to Max Event_Date 1

Status
Not open for further replies.

mwake

Programmer
Feb 12, 2004
151
US
If an examiner has an insurance claim transferred to him, a record is created in the Claim Event table. An event_date is recorded along with an event_code='COO3'. The new_value field contains the name of the examiner the claim has been transferred to. In the current SQL, I am selecting max new_value along with max event_date:

left outer join (select C.claimant_id, max(event_date) as event_date, max(new_val) as examiner from CLAIM_EVENT C
where event_code = 'C003' and event_date < dbo.SchedTo(@Prompt(SchedulePeriod), @Prompt(thru)) group by claimant_id) TRANSFERRED
on TRANSFERRED.claimant_id = CL.claimant_id

The problem is that the max new_value isn't always the new_value associated to the max event_date, so the wrong examiner name is returned. How do I get the new value associated to the max event_date??
 
As you noticed yourself MAX() is not taking values from the same record, MAX(field1) and MAX(field2) are taking the max values of each field independant of each other.

So one solution is to SELECT ...FROM table WHERE event_date = (SELECT MAX(event_date) FROM table)
Or you SELECT TOP 1 * FROM table ORDER BY event_date DESC, meaning LAST 1, as it is TOP in descending order.

By the way: Both solutions give you multiple records, if multiple records have the same maximum event_date.

Bye, Olaf.



 
Thanks Olaf. Your solution worked...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top