cancel
Showing results for 
Search instead for 
Did you mean: 

How to display/retrieve related row data on a maximum date formula

Former Member
0 Kudos

Here's data for one employee:

Charge Date Work Date Segment

11/05/08 11/28/08 TPLNDD

11/07/08 11/09/08 TPLNDN

11/21/08 11/22/08 TPLNDA

11/26/08 11/29/08 TPLNDN

12/11/08 12/14/08 TPLNDN

I want to calculate the maximum "charge date" where segments in "TPLNDD", "TPLNDA". Then, once this date is selected (11/21/08), I want to pick/retrieve the date which corresponds to that record (11/22/08).

I would like to avoid creating a group for the maximum charge date, because I want to be able to drill down to the details and see all the records.

Thanks, Ada

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Here you go..

Select emp_id, charge_date, Work_date, Segment from Employee_table e

where segment in ('TPLNDD', 'TPLNDA')

and charge_date = (select max(charge_date) from Employee_table where segment in ('TPLNDD', 'TPLNDA') and emp_id = e.emp_id)

Assuming you have the emp_id which is the primary key.

Thanks

-Azhar

Answers (0)