CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Paresh J
Paresh J

Reputation: 2419

Optimizing the SQL Query for Calculating account balance

I have following tables which are BankDetails and Transactiondetails. Using these two tables, I want to get the current balance of the account name.

Tables:

Create table Bankdetails
(
AccName varchar(50),
AccNo int,
OpBal numeric(18,2)
)

Create table Trandetails
(
AccNo int,
Amount numeric(18,2),
Trantype varchar(10)
)

Insert scripts for both tables:

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')

Output would be like this:

AccName Accno    CurrBal
  A     12345   30000.00 
  B     13345   25000.00
  C     14545   30000.00
  D     15045   20000.00

I need Account Holdername, Account No and current balance using the above two tables.

Below is my query, I want to get optimized query i.e without using subqueries if possible. Note: In my case, credit = amount added into account and debit = amount subracted from account.

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

My apologies for not following proper naming conventions for tables. Any help will be appreciated.

Thanks,

Paresh J

Upvotes: 2

Views: 4003

Answers (3)

Tolu
Tolu

Reputation: 175

For those that care about performance,here goes;

Select   B.AccName
        ,B.AccNo
        ,(B.OpBal + SUM( Case When TranType = 'Credit' Then Amount Else Amount* -1 End)) CurrBal
From Bankdetails B
Left Join Trandetails T
On B.AccNo = T.AccNo
Group By B.AccName, B.AccNo, B.OpBal;

SQL Fiddle

Upvotes: 0

Hoang Nguyen
Hoang Nguyen

Reputation: 79

SELECT 
    Bankdetails.AccName
    , Bankdetails.AccNo
    , Bankdetails.OpBal 
        + SUM(CASE WHEN TranType = 'Credit' THEN  Amount ELSE 0 END)
        - SUM(CASE WHEN TranType = 'Debit' THEN  Amount ELSE 0 END)
    AS 'CurrBal'
FROM Trandetails
    INNER JOIN Bankdetails ON Bankdetails.AccNo = Trandetails.AccNo
GROUP BY Bankdetails.AccNo, Bankdetails.AccName

Upvotes: -1

Felix Pamittan
Felix Pamittan

Reputation: 31879

The idea is to generate first the sum for each transaction type, Debit and Credit. With that, join it to the Bankdetails to compute for the current balance.

;with cte as(
    select
        AccNo,
        Credit = sum(case when TranType = 'Credit' then Amount else 0 end),
        Debit = sum(case when TranType = 'Debit' then Amount else 0 end)
    from Trandetails
    group by
        AccNo
)
select
    bd.AccName,
    bd.AccNo,
    CurrBal = bd.opBal - c.Debit + c.Credit
from BankDetails bd
inner join cte c
    on c.Accno = bd.Accno

Upvotes: 3

Related Questions