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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2013 Part … 176 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.