SQL Question

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

Thanks,

Jean Prejean
ppre_at_nospam.chevron.com (remove "nospam." to send e-mail) Received on Tue Oct 26 1999 - 10:33:51 CDT