i have a result set from an existing Oracle View attached with the question. i need to add 2 more columns to this view.
here is the brief business logic for those 2 dates
Service date: this is typically the hire_date, if a person is terminated and rehired within 12 months then its Service date is retained but if a person is rehired after 12 months of termination then its rehire_date is the Service_date
term service date: if a person is rehired after 12 months of termination then its previous hire date is the term service date
i am trying to write a logic something like
case when prev_endofday_status = 'TF' and hire_flag = 1 and hire_code = 'REHIRE' and add_months(term_date, 12) < hire_date then....?? test-data.xlsx
Oracle Database
Last Comment
Jayesh Acharya
8/22/2022 - Mon
Sean Stuber
what should your spreadsheet look like with the 2 new columns added?
Geert G
odd data, that employee got hired multiple times on the same date
that's probably why you can't get it to work
need_solution
ASKER
Attached is the spreadsheet with the 2 new columns.
And I should take back the logic i wrote for the new date columns,
Service date: this is the hire_date!
term service date: if a person is rehired after 12 months of termination then its previous hire date is the term service date test-data.xlsx