Solved

display data from previous rows

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query 9 45
Web Service from a stored procdure oracle 10 55
selective queries 7 30
PL SQL Search Across Columns 4 36
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

773 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