SQLPLUS MIND BENDER

Here is a problem for the group .....

Given two tables called alpha and bravo as follows:

          ALPHA                            BRAVO

    XDATE         QTY                  XDATE       QTY

  04-JUN-93       20                 03-JUN-93     25
  05-JUN-93       30                 06-JUN-93     30
  06-JUN-93       25                 07-JUN-93     40
  08-JUN-93       20                 08-JUN-93     25
  10-JUN-93       10                 08-JUN-93     10

I want to create a consolidated table/view called COCOA that looks like:

                          COCOA

              ALPHA    ALPHA     BRAVO    BRAVO  BRAVO-ALPHA
   XDATE       QTY     TOTAL      QTY     TOTAL   VARIANCE

  03-JUN-93      0        0        25       25       25  
  O4-JUN-93     20       20         0       25        5
  05-JUN-93     30       50         0       25      -25
  06-JUN-93     25       75        30       55      -20
  07-JUN-93      0       75        40       95       20
  08-JUN-93     20       95        35      130       35
  10-JUN-93     10      105         0      130       25

In general, the rules are:

• The only relationship between the two tables is the date field.
  • An entry for a given date in one table does not mean that there will also be an entry in the other table.
  • Some dates are missing
  • The may be one to n entries in a source table for the same day. These need to be consolidated into a single entry before the running total is calculated.

In summary, I am seeking a single table that will consolidate the two source tables and add cumulative totals for each as well as a a final variance between the totals.

I have managed to build the output view by using several intermediate views to initially consolidate information from each table but my solution is not 'elegant' enough. There must be a simpiler method of doing it.

Thoughts?