display data from previous rows

need_solution
need_solution used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
what should your spreadsheet look like with the 2 new columns added?
Geert GOracle dba
Top Expert 2009

Commented:
odd data, that employee got hired multiple times on the same date

that's probably why you can't get it to work

Author

Commented:
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
Geert GOracle dba
Top Expert 2009

Commented:
your data doesn't make sense !
column B and M both have the title hire_date

and you indicate service date is the hire date

leave out all the columns which are not used for this problem
it just confuses
Technichal Consultant
Commented:
Hi
To recap what you have I listed some assumptions

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


service_date1 = hire_date
service_date2 = hire_date when rehired within 12 months
service_date3 = hire_date2 (re_hire_date) when rehired after 12 months


term service date:
if a person is rehired after 12 months of termination
then its previous hire date is the term service date

term_date1 = term_date
term_date2 = hire_date  when rehired after 12 months (the hire date would of been the previous hire date)

you can then write sql like, the test1 table is just a table i created to test the results, but you would want to replace that with the actual table. If the sql looks good, then I would suggest you create this as a view

select a.*
, case hire_code
      when 'REHIRE' then
            case when (add_months(a.term_date, 12) < a.hire_date2)
                        then hire_date -- this is the original hire_date
                        else hire_date2 -- this is the re-hire_date
                        end
      else
            hire_date -- this is the original hire_date
      end   SERVICE_DATE
, case hire_code
      when 'REHIRE' then
            case when (add_months(a.term_date, 12) < a.hire_date2)
                        then cast(NULL as date)
                        else hire_date -- this is the previous hire_date
                        end
      else
            term_date -- this is the original term_date
      end   term_date2  
from test1 a
;

contact me directly if you would like some more insights. BetterMarketingSolution @ gmail.com

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial