New_Value Associated to Max Event_Date 1

[mwake]

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??

 

[Olaf Doschke]

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.

 

[mwake]

Thanks Olaf. Your solution worked...