Solved

oracle query on a dataware house table-- challenging query

Posted on 2016-08-26
5
69 Views
Last Modified: 2016-08-27
Hi Team ,

Below is the query that i have written to fetch the latest record for a empid , based on the effective_date and effective_seq. the requirement is an employee may have many changes happening in regard to his salary , position etc. this recorded by the means of effective date , effective sequence , status.

below is the table structure and data:
Job table data:

EMPID               EMPREC EFFECTIVE EFFECTIVE_SEQ S ACTION
--------------- ---------- --------- ------------- - --------------------
001                      0 01-JAN-16             0 A Hire
001                      0 31-MAR-16             0 A Pay-Change
001                      0 31-MAR-16             1 A Line-manager-Change
001                      0 01-APR-16             0 I Termination
002                      0 31-MAR-16             2 I Terminated
002                      0 31-MAR-16             0 A Hire
002                      0 31-MAR-16             1 A Pay-Change
003                      0 31-MAR-16             1 A Pay-change
003                      0 31-MAR-16             0 A Hire
004                      0 29-MAR-16             0 A Hire
004                      0 30-MAR-16             0 A Pay-Change

note the effective seq column is incremented by for each change on the same day.

for a given date say 31-mar-2016 , i must pull out all the latest record for the empoyees.

expected out put is:

003                      0 31-MAR-16             1 A Pay-change
001                      0 31-MAR-16             1 A Line-manager-Change
004                      0 30-MAR-16             0 A Pay-Change


query written is :

Select *
from
(
Select Empid , Effective_date , Effective_seq, Status, Action ,
row_number() over (partition by Empid order by Effective_seq desc  , effective_date desc) rk
from job)
where rk=1  and status ='A' and Effective_date <=to_date('31-MAR-2016','DD-MON-YYYY');


EMPID           EFFECTIVE EFFECTIVE_SEQ S ACTION                       RK
--------------- --------- ------------- - -------------------- ----------
001             31-MAR-16             1 A Line-manager-Change           1
003             31-MAR-16             1 A Pay-change                    1
004             30-MAR-16             0 A Pay-change                    1


I want to know whether the approach i have followed is correct , is there a better way of writing the above query , Really appreciate for any feedback.
0
Comment
Question by:sam_2012
[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
  • 2
  • 2
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 41772025
that is probably the correct approach.

However, if you have an index on Empid,Effective_seq, effective_date
or if you are using 12c and have these columns in a in-memory column stores, then it's possble that using a subquery to find the max rows might be more efficient and then querying the table again to retrieve the rest of the table columns.

you'll have to test both ways to determine which is better
0
 
LVL 32

Expert Comment

by:awking00
ID: 41772048
An observation note (no points, please) - If effective_date contains a time element then Effective_date <=to_date('31-MAR-2016','DD-MON-YYYY') would not include 31-MAR-2016 (except for midnight).  You may want to consider using < to_date('01-APR-2016','DD-MON-YYYY') to be safe.
0
 

Author Comment

by:sam_2012
ID: 41772544
Hi sdStuber,

Below is the query using subquery , can help me whether this query is fine

Select *
from job a
where effective_seq =Any (Select max(effective_seq) from job b where
                                             b.empid=a.empid
                          )
and effective_date =Any(Select max(effective_date) from job c
                                          where c.empid=a.empid and c.status=a.status)
and a.status='A'   and a.effective_date <=to_date('31-MAR-2016','DD-MON-YYYY')
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 41772551
no the functional equivalent to the analytic version would be something like this...


SELECT *
  FROM job a
 WHERE (effective_seq, effective_date) IN
           (SELECT MAX(effective_seq), MAX(effective_date) KEEP (DENSE_RANK FIRST ORDER BY effective_seq DESC)
              FROM job b
             WHERE b.empid = a.empid)
   AND a.status = 'A'
   AND a.effective_date <= TO_DATE('31-MAR-2016', 'DD-MON-YYYY');
0
 

Author Closing Comment

by:sam_2012
ID: 41773127
awesome
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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…
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…
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.
Suggested Courses

627 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