I had posted a question last week and I did accept a solution but looks like I cant completely achieve what I need, may be because I could not explain the logic properly. So here's one more try.
Columns A-H are the columns in existing Oracle view, I need to add an additional column, column I. Column J is what I am getting from my current script.
The business logic is:
if an employee is rehired within 12 months of termination then its term_Service_date (new field) should be its original hire_effect_date. but if he is rehired after 12 months then his term_Service_date (new field) should be his new hire_effect_date.
using my current script, i can get the correct term_Service_dates on the rows where it satisfies the logic but i want to repeat the same hire_effect_date until it finds the next hire_effect_date and if that hire_effect_date is > term_date + 12 months.
employee, hire_date, term_flag,
term_date, term_code, hire_flag, hire_effect_date, hire_code,
when 1 then
case when (add_months(term_date, 12) < hire_effect_date)
employee = 1234
order by effect_date desc