I have provided the code and an example of the expected result set...so I am not sure what doesn't make sense? Maybe a step-by-step would help...it took me several conversations with the user to finally get it.
1. Pull the records from each group with the latest c_dt
This should return these two records:
'11111','2010-09-06 12:00AM','25'
'11111','2010-10-06 12:00AM','23'
Where the C_Id is the same, but the c_rep is different.
2. Nest the first query, to pull matching C_id's for final query, and then pull the latest "last_dt" that is earlier than the c_dt from the first set of records:
'11111','2010-09-06 12:00AM','2010-09-04 12:00AM','25'
'11111','2010-10-06 12:00AM','2010-10-04 12:00AM','23'
Basically, I want to pull the last transactions for a specific group, using c_dt, then find another type of "last transaction" that occurred prior to that last transaction. I hope this helps.
Thanks,