Solved

display data from previous rows

Posted on 2016-08-02
5
66 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 73

Expert Comment

by:sdstuber
Comment Utility
what should your spreadsheet look like with the 2 new columns added?
0
 
LVL 36

Expert Comment

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

Expert Comment

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now