max aggregate returning multiple rows

Robin Olsen · Mar 20, 2024 7:27PM

Hello

I work in a Windows environment and I am using Oracle HCM Analysis tool to alter a benefits report to only show the latest benefit by date.

I am using the 'Benefits - Enrollment Real Time' subject area. The report is to display the information per employee.

The problem is I use the max function to try and restrict records to only the record with the latest date. However, I am getting multiple rows returned (as if the max function was completely ignored). I need to know how to completely omit the records where the date is not the latest date.

The 'amount' value is also displayed on the report and there are multiple records per benefit (example: a employee has Basic Group Life for 150,000 which was effective on 01/01/2024 - on 01/15/2024 the employee reduced the benefit to 130,000 - the report should only show the entry for the 15th and the amount of 130,000 but it will show both records instead.)

Can anyone tell me how to correct this? I have tried using group by, I have tried not using group by. I would write this directly from the tables and not use this analysis tool but I have no idea of the actual table names this data comes from.

Thanks in advance for any and all tips and tricks shared...