title: Optimizing the SQL Query for Calculating account balance
body: <p>I have following tables which are BankDetails and Transactiondetails. Using these two tables, I want to get the current balance of the account name.</p>
  
  <p>Tables:</p>
  
  <pre><code>Create table Bankdetails
  (
  AccName varchar(50),
  AccNo int,
  OpBal numeric(18,2)
  )
  
  Create table Trandetails
  (
  AccNo int,
  Amount numeric(18,2),
  Trantype varchar(10)
  )
  </code></pre>
  
  <p>Insert scripts for both tables:</p>
  
  <pre><code>insert into Bankdetails values('A', 12345, 30000.00)
  insert into Bankdetails values('B', 13345, 30000.00)
  insert into Bankdetails values('C', 14545, 30000.00)
  insert into Bankdetails values('D', 15045, 30000.00)
  
  insert into Trandetails values(12345, 5000.00, 'Credit')
  insert into Trandetails values(13345, 5000.00, 'Debit')
  insert into Trandetails values(15045, 5000.00, 'Debit')
  insert into Trandetails values(13345, 5000.00, 'Credit')
  insert into Trandetails values(12345, 5000.00, 'Debit')
  insert into Trandetails values(13345, 5000.00, 'Debit')
  insert into Trandetails values(14545, 5000.00, 'Credit')
  insert into Trandetails values(15045, 5000.00, 'Debit')
  insert into Trandetails values(14545, 5000.00, 'Debit')
  </code></pre>
  
  <p>Output would be like this:</p>
  
  <pre><code>AccName Accno    CurrBal
    A     12345   30000.00 
    B     13345   25000.00
    C     14545   30000.00
    D     15045   20000.00
  </code></pre>
  
  <p>I need Account Holdername, Account No and current balance using the above two tables.</p>
  
  <p>Below is my query, I want to get optimized query i.e without using subqueries if possible.
  <strong>Note:</strong> In my case, credit = amount added into account and debit = amount subracted from account.</p>
  
  <pre><code>Select bd.accname, bd.accno, 
  (bd.opbal - isnull((select SUM(Amount) from Trandetails where Trantype = 'Debit' and accno = bd.accno group by accno),0) + isnull((select SUM(Amount) from Trandetails where Trantype = 'Credit'  and accno = bd.accno group by accno),0)) as Bal
  From Bankdetails BD inner join Trandetails TD on td.AccNo = bd.AccNo
  group by bd.accno, bd.accname, bd.opbal
  </code></pre>
  
  <p>My apologies for not following proper naming conventions for tables. Any help will be appreciated.</p>
  
  <p>Thanks,</p>
  
  <p>Paresh J</p>