Solved

oracle query on a dataware house table-- challenging query

Posted on 2016-08-26
5
46 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
  • 2
  • 2
5 Comments
 
LVL 73

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 31

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 73

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

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

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…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

757 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

18 Experts available now in Live!

Get 1:1 Help Now