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
5
Medium Priority
?
101 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
[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
  • Learn & ask questions
5 Comments
 
LVL 74

Expert Comment

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

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 38

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 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

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…

715 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