Ordering a joined and concatenated table (plus running balance)

[#1]

I have to join and concatenate 4 source tables to produce one Facts table.  I would then like to sort the Facts table based on Date field so I can produce a running balance field.

So far I have

Facts:

     Load

          Date,    

          ...;

SQL ... From ...;

Left join (Facts)

     Load

          Amount,

          ...;

SQL ... From ...;

Facts2:

     Load

          Date,

          ...;

SQL ... From ...;

Left join (Facts2)

     Load

          Amount,

          ...;

SQL ... From ...;

Concatenate (Facts)

Load

     *

Resident Facts2;

Drop Facts2;

This creates the required table, however I'm not sure how I can:

1. Sort the table using Date
2. Create a new column called Balance which is Amount + peek(Balance)

Any help will be much appreciated.

Matt

Clarification by: matt.lj.morris

So Thanks to Gysbert I have now got my data ordered by Date, but I'm having lots of problems calculating the running balance.

My four attempts are:

Amount + alt( previous(Amount),0 ) as Balance1

if( isnull( previous(Amount) ), Amount, Amount + Peek(Balance2) ) As Balance2

numsum(Amount,peek(Balance3)) AS Balance3

if( rowno()=0, Amount, Amount + peek(Balance4) ) as Balance4

And the results of each are in the attached image.

[Gysbert_Wassenaar]

Try changing the last load statement to:

Load *,

Amount + alt(peek('Balance'),0) as Balance,

Resident Facts2 order by Date;

drop table Facts2;

[#3]

Dear Gysbert,

Thanks for your speedy response.  Previewing the Facts table I see that it has ordered the rows by date but there doesn't seem to be any values in Balance...

Matt