Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# display data from previous rows

Posted on 2016-08-02
Medium Priority
101 Views
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
0
Question by:need_solution
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 74

Expert Comment

ID: 41739986
0

LVL 38

Expert Comment

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

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

Author Comment

ID: 41740606
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
0

LVL 38

Expert Comment

ID: 41741866
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
0

LVL 2

Accepted Solution

jhacharya earned 2000 total points
ID: 41744970
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
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
###### Suggested Courses
Course of the Month7 days, 21 hours left to enroll