Another Messed Up Query

From: <mrdjmagnet_at_aol.com>
Date: Fri, 9 Jan 2009 12:21:01 -0800 (PST)
Message-ID: <53dc4303-2ba2-4ad1-acbb-8ec7ad0a5b87_at_y1g2000pra.googlegroups.com>



This must be a bad day for me. Here are the 2 tables:

TABLE A



ID MONTH YEAR TOTAL_RETURN
A      1             1997               56
A      2             1997               88
A      3             1997               98
A      1             1998               34
A      2             1998               11
A      3             1998               97

TABLE B



ID PRICE DATE_ENTERED
A     .56            01/14/1997
A     .98            01/18/1997
A     .30            02/23/1997
A     .52            01/14/1998
A     .11            02/03/1998
A     .34            02/09/1998

Ok, so you see the pattern which occurs for thousands of records. TABLE A has data by Month & Year.
TABLE B has multiple records per month.
Tables are joined by ID.

I need to get the ID & Total Return from TABLE A and Price & MAX (LAST_DAY(date_entered)) from TABLE B FOR EACH MONTH/YEAR in TABLE A.

So, the result should be:
A .98 56 01/31/1997
A .30 88 02/28/1997
.
.
.

I need the MAX date in TABLE B for each MONTH/YEAR combination in TABLE A..... Possible in 1 query??? Tried analytical functions with no luck. Received on Fri Jan 09 2009 - 14:21:01 CST

Original text of this message