Income, Expense Query in SQL Server.

Question

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.

Basit Khan · Answer

drop table Income go create table Income ( IncomeNo int , IncomeDate date , IncomeDepositeinBank varchar(100) , IncomeAmount decimal(10,2) ) go 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 go select * from Income go drop table Expense go create table Expense ( ExpenseNo int , ExpenseDate date , ExpenseSupplier varchar(100) , ExpenseAmount decimal(10,2) , ExpenseIssueFromBank varchar(100) ) go 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' go select * from Expense go drop table BankInterTransfer go create table BankInterTransfer ( BankInterNo int, BankInterDate date, BankInterFromBank varchar(100), BankInterToBank varchar(100), BankInterAmount decimal(10,2), BankInterRemark varchar(100) ) go 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' go select * from BankInterTransfer go 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

Kannadasan G · Answer

Can you please upload the table script with some sample data here.