2 Replies Latest reply: Nov 23, 2012 11:53 AM by Matt Morris RSS

    Ordering a joined and concatenated table (plus running balance)

      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.