Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 76
  • Last Modified:

oracle query on a dataware house table-- challenging query

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
sam_2012
Asked:
sam_2012
  • 2
  • 2
1 Solution
 
sdstuberCommented:
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
 
awking00Commented:
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
 
sam_2012Author Commented:
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
 
sdstuberCommented:
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
 
sam_2012Author Commented:
awesome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now