Re: SQL Question

Jean Prejean <jean_at_nospam> wrote in message news:BckVOIduqnBpvByeZmYrHreJH3e1_at_4ax.com...
> I am looking for a sql statement that will give me a daily report of
> records. However, on some of the days, there is no daily activity but
> the number should carry forward. Below is some sample data being
> used. In table one I have a daily activity of volume. In table two I
> store the rate, however the rate is stored whenever it changes.
>
> For example:
>
> IN TABLE ONE I HAVE THE FOLLOWING DATA
> MR_KEY PROD_DATE VOLUME
> --------- --------- ---------------------
> 3 01-OCT-99 3551
> 3 02-OCT-99 3582
> 3 03-OCT-99 3478
> 3 04-OCT-99 3556
> 3 05-OCT-99 3618.99
> 3 06-OCT-99 3565
> 3 07-OCT-99 3629
> 3 08-OCT-99 3596.01
> 3 09-OCT-99 3612
> 3 10-OCT-99 3597.01
>
> IN TABLE TWO I HAVE THE FOLLOWING DATA
> MR_KEY PROD_DATE RATE
> --------- --------- -----------
> 3 01-OCT-99 2828
> 3 05-OCT-99 2900
>
> AN OUTER JOIN GIVES THE FOLLOWING RESULTS:
>
> MR_KEY PROD_DATE RATE VOLUME
> --------- --------- ----------- ---------------------
> 3 01-OCT-99 2828 3551
> 3 02-OCT-99 NULL 3582
> 3 03-OCT-99 NULL 3478
> 3 04-OCT-99 NULL 3556
> 3 05-OCT-99 2900 3618.99
> 3 06-OCT-99 NULL 3565
> 3 07-OCT-99 NULL 3629
> 3 08-OCT-99 NULL 3596.01
> 3 09-OCT-99 NULL 3612
> 3 10-OCT-99 NULL 3597.01
>
>
> THIS IS WHAT I WANT MY END RESULT TO BE
> MR_KEY PROD_DATE RATE VOLUME
> --------- --------- ----------- ---------------------
> 3 01-OCT-99 2828 3551
> 3 02-OCT-99 2828 3582
> 3 03-OCT-99 2828 3478
> 3 04-OCT-99 2828 3556
> 3 05-OCT-99 2900 3618.99
> 3 06-OCT-99 2900 3565
> 3 07-OCT-99 2900 3629
> 3 08-OCT-99 2900 3596.01
> 3 09-OCT-99 2900 3612
> 3 10-OCT-99 2900 3597.01
SQL> select o.mr_key, o.prod_date, t.rate, o.volume   2 from one o, two t
  3 where o.mr_key=t.mr_key

  4      and t.prod_date=(select max(prod_date) from two
  5                         where prod_date<=o.prod_date);

   MR_KEY PROD_DATE      RATE    VOLUME
--------- --------- --------- ---------
        3 01-OCT-99      2828      3551
        3 02-OCT-99      2828      3582
        3 03-OCT-99      2828      3478
        3 04-OCT-99      2828      3556
        3 05-OCT-99      2900   3618.99
        3 06-OCT-99      2900      3565
        3 07-OCT-99      2900      3629
        3 08-OCT-99      2900   3596.01
        3 09-OCT-99      2900      3612
        3 10-OCT-99      2900   3597.01

10 rows selected. Received on Tue Oct 26 1999 - 21:49:20 CDT