Re: Another Messed Up Query

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 10 Jan 2009 23:42:30 +0100
Message-ID: <49692456.6030905_at_gmail.com>

mrdjmagnet_at_aol.com schrieb:
> 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.

As Charles already pointed out, the database version should be provided for some meaningful advice, assuming you are on more or less recent version, i would probably do something like

SQL> select a.id,b.price,a.total_return,b.date_entered

   2 from a, (

   3      select id,
   4      to_number(to_char(date_entered,'yyyy')) year,
   5      to_number(to_char(date_entered,'mm')) month,
   6      max(last_day(date_entered)) date_entered,
   7      max(price) keep(dense_rank last order by date_entered) price
   8      from b
   9      group by 

id,to_number(to_char(date_entered,'yyyy')),to_number(to_char(date_entered,'mm'))   10 ) b
  11 where a.id=b.id and a.month=b.month and a.year=b.year   12 ;

I PRICE TOTAL_RETURN DATE_ENTER
- ---------- ------------ ----------

A        .98           56 01/31/1997
A         .3           88 02/28/1997
A        .52           34 01/31/1998
A        .34           11 02/28/1998

You may consider to use an outer join to inline view ( or vice versa) depending on your data and business logic.

Best regards

Maxim Received on Sat Jan 10 2009 - 16:42:30 CST