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
  • Learn & ask questions
Solved

display data from previous rows

Posted on 2016-08-02
5
87 Views
Last Modified: 2016-08-08
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
Comment
Question by:need_solution
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 41739986
what should your spreadsheet look like with the 2 new columns added?
0
 
LVL 37

Expert Comment

by:Geert Gruwez
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

by:need_solution
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 37

Expert Comment

by:Geert Gruwez
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

by:
jhacharya earned 500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question