display data from previous rows
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
odd data, that employee got hired multiple times on the same date
that's probably why you can't get it to work
that's probably why you can't get it to work
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
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
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
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
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial