Income, Expense Query in SQL Server.

Hi,

I have three tables. Income, Expense and BankInterTransfer.

Income

IncomeNo

IncomeDate

IncomeDepositeinBank ----> Bank Name will show from Bank Master

IncomeAmount (will add in Bank)

Expense

ExpenseNo

ExpenseDate

ExpenseSupplier

ExpenseAmount (Will deduct)

ExpenseIssueFromBank ----> Bank Name will show from Bank Master

BankInterTransfer

BankInterNo

BankInterDate

BankInterFromBank ----> Bank Name will show from Bank Master

BankInterToBank ----> Bank Name will show from Bank Master

BankInterAmount (Will add from one bank and reduce from another Bank)

BankInterRemark

I want a query where it should show BankName, IncomeAmount, ExpenseAmount and BalanceAmount columns with three tables.

Thanks

Basit.

drop table Income

create table Income

(

IncomeNo int ,

IncomeDate date ,

IncomeDepositeinBank varchar(100) ,

IncomeAmount decimal(10,2)

)

insert into Income select 11111,'2019-07-09','HDFC Bank',102234.54

insert into Income select 11112,'2019-07-10','HDFC Bank',1266.80

insert into Income select 11113,'2019-07-11','HDFC Bank',234445.11

select * from Income

drop table Expense

create table Expense

(

ExpenseNo int ,

ExpenseDate date ,

ExpenseSupplier varchar(100) ,

ExpenseAmount decimal(10,2) ,

ExpenseIssueFromBank varchar(100)

)

insert into Expense select 1234,'2019-04-10','ok supplies',11234.56,'HDFC Bank'

insert into Expense select 1235,'2019-04-11','ok supplies',5003.90,'HDFC Bank'

insert into Expense select 1236,'2019-04-12','ok supplies',1111.23,'HDFC Bank'

select * from Expense

drop table BankInterTransfer

create table BankInterTransfer

(

BankInterNo int,

BankInterDate date,

BankInterFromBank varchar(100),

BankInterToBank varchar(100),

BankInterAmount decimal(10,2),

BankInterRemark varchar(100)

)

insert into BankInterTransfer select 123,'2019-04-09','Axis Bank','HDFC Bank',100000.90,'all fine'

insert into BankInterTransfer select 124,'2019-06-09','Axis Bank','HDFC Bank',1002222.90,'ok'

insert into BankInterTransfer select 134,'2019-06-13','Axis Bank','HDFC Bank',2002222.90,'not bad'

select * from BankInterTransfer

select * from Income

select * from Expense

select * from BankInterTransfer

--See the below Query

select IncomeDepositeinBank,SUM(IncomeAmount) as TotalIncome from Income group by IncomeDepositeinBank

select ExpenseIssueFromBank,SUM(ExpenseAmount) as TotalExpense from Expense group by ExpenseIssueFromBank

select BankInterFromBank,BankInterTobank, SUM(BankInterAmount) as TotalAmount from BankInterTransfer group by BankInterFromBank,BankInterTobank

The below result looking for.

BankName	TotalIncome	TotalExpense	Balance
HDFC Bank	337,946.45	17,349.69	320,596.76

But When Bank InterTransfer Apply the Result is below Becoz from Axix Transfer 3104446.70 to HDFC

BankName	TotalIncome	TotalExpense	Balance
HDFC Bank	3,442,393.15	17,349.69	3,425,043.46