PL/SQL - selecting the MAX() of a date field with criteria from another table 1

[djpingpong]

So I have 2 tables:
PERSON -> is a table of basic info regarding members in the company (unique key here is PERSON_ID)
sample records:
PERSON_ID DEPT
123 abc
111 abc
100 bdc
200 cda
140 abc

EARN_HIST -> is a table of historical earnings for each member (unique key here is PERSON_ID, EARN_DATE) and has a 1-to-many relationship to PERSON table
sample records:
PERSON_ID EARN_DATE EARNINGS
123 2002/1/1 $1000
123 2010/1/1 $2000
100 2009/1/1 $15000
100 2011/1/1 $10000
111 2008/1/1 $5000
111 2008/7/1 $7000
111 2012/1/1 $10000
140 2010/6/1 $5000
140 2010/12/1 $7500
etc, etc, etc

My goal is to retrieve the most current Earning record for each member where DEPT = 'abc'
so my expected result is:
PERSON_ID EARN_DATE EARNINGS
123 2010/1/1 $2000
111 2008/7/1 $7000
140 2010/12/1 $7500

can this be done be just a query? or do I have to define a Store Procedure and/or Function to achieve my results?
any help on this would be greatly appreciated

 

[fredericofonseca]

what have you tried so far?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[djpingpong]

Well... i was successful in creating a function that would return the most current Earning record when passing in a PERSON_ID

CREATE OR REPLACE FUNCTION GET_CURRENT_EARN(inMbr in person.person_id%type)
  RETURN NUMBER IS

  v_Event number(5);

begin
  select a.EARNINGS
    into v_Event
    from EARN_HIST a
   where a.EARN_DATE = (select max(d.earn_date)
                           from EARN_HIST d
                          where a.PERSON_ID = d.PERSON_ID
                          group by d.PERSON_ID)
     and a.PERSON_ID = inMbr

  return v_Event;

end GET_CURRENT_EARN;

what I wasn't successful in was when I try to call this function with this SQL statement

SELECT person_id, GET_CURRENT_EARN(person_id) FROM PERSON WHERE DEPT = 'abc';

I get the following error:
ORA-01422: exact fetch returns more than requested number of rows

so what do I need to do with this function to return the latest earnings amount for all the members that I'm querying based on:

SELECT person_id FROM PERSON WHERE DEPT = 'abc';

or is there any other method besides using a function?

 

[carp]

I would start by getting rid of the GROUP BY in your subquery. Since you are only pulling the MAX in your select list, you do not need to group anything. Also, what is the primary key on your history table? It may be that you have a duplicate person_id/earn_date situation in your table.

 

[djpingpong]

thx carp,

you were right about the duplicates and the group by
so i just adjusted the function to return the TOP 1 record and its working now

CREATE OR REPLACE FUNCTION GET_CURRENT_EARN(inMbr in person.person_id%type)
  RETURN NUMBER IS

  v_Event number(5);

begin
  select *
    into v_Event
  from (select a.EARNINGS
    into v_Event
    from EARN_HIST a
   where a.EARN_DATE = (select max(d.earn_date)
                           from EARN_HIST d
                          where a.PERSON_ID = d.PERSON_ID
                          group by d.PERSON_ID)
     and a.PERSON_ID = inMbr)
  where rownum = 1;

  return v_Event;

end GET_CURRENT_EARN;

thanks again

 

[fredericofonseca]

and going back to the other point of your question.

try

SELECT person_id
      ,a.EARNINGS
from PERSON b
INNER JOIN EARN_HIST a
on a.person_id = b.person_id
and a.EARN_DATE = (select max(d.earn_date)
                     from EARN_HIST d
                    where a.PERSON_ID = d.PERSON_ID
                    group by d.PERSON_ID)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[carp]

And just to avoid future issues, if there are not supposed to be duplicate records in the table I would see what I could do about getting rid of them. And while you're at it, try to figure out how they are getting in there to begin with and eliminate the source.

As for a query, you could also try:

SELECT p.person_id, e.earnings
  FROM person p
       INNER JOIN earn_hist e
          ON p.person_id = e.person_id
             AND p.dept = 'abc'
       INNER JOIN (SELECT person_id, max(earn_date)
                     FROM earn_hist 
                    GROUP BY person_id) v
          ON e.person_id = v.person_id
             AND e.earn_date = v.earn_date;

Disclaimer - I haven't run this query; just cobbled it up on the fly. But I think it should work.