title: Find the balance of transaction of each customer starting after last zero balance to latest balance
body: <p>I am using SQL Server 2005. I have a two tables -  </p>
  
  <pre><code>1. tbl_Customer  
    - CustId INT,  
    - Name VARCHAR(40)  
  2. tbl_Transaction  
    - TransId INT,  
    - CustId INT,  
    - TranDate DATETIME  
    - DebitAmount NUMERIC(8,2),  
    - CreditAmount NUMERIC(8,2)  
  </code></pre>
  
  <p>Data of <strong>tbl_Customer</strong></p>
  
  <pre><code>CustId    Name
  
  1        ST  
  2        JS  
  3        MA
  </code></pre>
  
  <p>Data of <strong>tbl_Transaction</strong></p>
  
  <pre><code>TransId CustId  Tdate       DtAmt   CrAmt
  101     1       1/1/2012    250     100
  102     1       1/2/2012    0       100
  103     1       1/2/2012    0       50
  104     2       1/2/2012    400     200
  105     2       1/3/2012    0       150
  106     2       1/3/2012    0       40
  107     2       1/4/2012    0       10
  108     1       1/1/2012    350     50
  109     1       1/2/2012    0       200
  110     1       1/2/2012    0       100
  111     2       1/10/2012   500     300
  112     2       1/10/2012   0       120
  </code></pre>
  
  <p>I want to find balance of each customer starting from last zero balance. A balance is (DebitAmount - CreditAmount) for first row and then (Balance - CreditAmount) for next rows. So after the query the result should look like this -</p>
  
  <pre><code>TransId CustId  Transdate   DtAmt   CrAmt      Balance
  108     1       1/1/2012    350     50      300
  109     1       1/2/2012    0       200     100
  110     1       1/2/2012    0       100     0
  111     2       1/10/2012   500     300     200
  112     2       1/11/2012   0       120     80
  </code></pre>
  
  <p>Now main thing is, I want to search customers and display latest balance of each customer in result. If there is no balance (no transaction is available in tbl_transaction), the balance should be null. So the final result should look like this.</p>
  
  <pre><code>CustId  Name    Balance
  1       ST      0
  2       JS      80
  3       MA      NULL
  </code></pre>
  
  <p>I hope I have elaborated quite clearly what I want to achieve. Please give me directions on how to achieve this. I am new to SQL and learning bits and bytes of RDBMS. Thank you in advance.</p>
  
  <p>Ritesh</p>