Hi, I have a dataset with employee id, start date, role code, role group and status.  empid start dt role code role group status 101 01-Jan-20 231 AD1 A 101 01-May-20 231 AD1 A 101 01-Aug-20 232 AD1 A 101 01-Sep-20 301   A 101 01-Nov-20 301   A 102 01-Apr-21 231 AD1 A 102 01-Jun-21 231 AD1 D 102 01-Aug-22 231 AD1 A     From this table, I want to define another date variable 'effective' - if employee has same role group or role code then effective should be minimum(very first) start  date. -if employee status='D' meaning not Active then effective date should be that of previous record. -if there is any entry after status=D meaning(rejoined) then effective should be start dt of new role.   Expected final table is  empid start dt role code role group status Effective 101 01-Jan-20 231 AD1 A 01-Jan-20 101 01-May-20 231 AD1 A 01-Jan-20 101 01-Aug-20 232 AD1 A 01-Jan-20 101 01-Sep-20 301   A 01-Sep-20 101 01-Nov-20 301   A 01-Sep-20 102 01-Apr-21 231 AD1 A 01-Apr-21 102 01-Jun-21 231 AD1 D 01-Apr-21 102 01-Aug-22 231 AD1 A 01-Aug-22   I tried lag function, first. and last. , but nothing worked. Your help is truly appreciated!   Thanks, ... View more