Income, Expense Query in SQL Server

basit0079 · July 8, 2019, 5:13pm

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.

harishgg1 · July 9, 2019, 9:27am

hi

i am trying to understand this ...please help me understand

i created sample data

drop create sample data ...

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 11111,'2019-07-10','HDFC Bank',1266.80
insert into Income select 11111,'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 1234,'2019-04-11','ok supplies',5003.90,'HDFC Bank'
insert into Expense select 1234,'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

selecting the data

harishgg1 · July 9, 2019, 9:52am

this joins i am -- NOT SURE -- what bank maps to what bank in the tables

SELECT * 
FROM   income a 
       JOIN expense b 
         ON a.incomedepositeinbank = b.expenseissuefrombank 
       JOIN bankintertransfer c 
         ON a.incomedepositeinbank = c.bankintertobank

basit0079 · July 9, 2019, 10:10am

harishgg1:

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 11111,'2019-07-10','HDFC Bank',1266.80 insert into Income select 11111,'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 1234,'2019-04-11','ok supplies',5003.90,'HDFC Bank' insert into Expense select 1234,'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

Many Thanks,

See the below query giving you result.

Total Income in Bank

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

Total Expense For Bank

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

Total Transfer from one bank to another bank

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

What i want is combination of one query where i can see balance in each bank.

BankName, TotalIncome, TotalExpense and BalanceAmount.

The BankInterTransfer table will transfer amount from one bank to another bank.

Thanks
Basit.

harishgg1 · July 9, 2019, 10:28am

hi basit

is this ok ???

SQL ...

SELECT incomedepositeinbank, 
       ''                AS ToBank, 
       Sum(incomeamount) AS TotalIncome 
FROM   income 
GROUP  BY incomedepositeinbank 
UNION ALL 
SELECT expenseissuefrombank, 
       ''                 AS ToBank, 
       Sum(expenseamount) AS TotalExpense 
FROM   expense 
GROUP  BY expenseissuefrombank 
UNION ALL 
SELECT bankinterfrombank, 
       bankintertobank, 
       Sum(bankinteramount) AS TotalAmount 
FROM   bankintertransfer 
GROUP  BY bankinterfrombank, 
          bankintertobank

basit0079 · July 9, 2019, 10:47am

Many Thanks.

No this is not the result.

The columns in the results are.

Bank Name TotalIncome, TotalExpense and (TotalIncome-TotalExpense) as TotalBalance

See 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

Thanks
Basit.

harishgg1 · July 9, 2019, 11:08am

hi basit

i tried it based on your feedback

SQL ..

;WITH cteincome 
     AS (SELECT incomedepositeinbank AS bankname, 
                Sum(incomeamount)    AS TotalIncome 
         FROM   income 
         GROUP  BY incomedepositeinbank), 
     cteexpense 
     AS (SELECT expenseissuefrombank AS bankname, 
                Sum(expenseamount)   AS TotalExpense 
         FROM   expense 
         GROUP  BY expenseissuefrombank), 
     cteinter 
     AS (SELECT bankinterfrombank, 
                bankintertobank, 
                Sum(bankinteramount) AS TotalAmount 
         FROM   bankintertransfer 
         GROUP  BY bankinterfrombank, 
                   bankintertobank) 
SELECT 'Normal',a.bankname, 
       a.totalincome, 
       b.totalexpense, 
       a.totalincome - b.totalexpense AS Balance 
FROM   cteincome a 
       JOIN cteexpense b 
         ON a.bankname = b.bankname 
UNION ALL 
SELECT 'Inter',a.bankintertobank, 
       c.totalincome + a.totalamount                  AS totalincome, 
       b.totalexpense, 
       c.totalincome + a.totalamount - b.totalexpense AS Balance 
FROM   cteinter a 
       JOIN cteexpense b 
         ON a.bankintertobank = b.bankname 
       JOIN cteincome c 
         ON a.bankintertobank = b.bankname 

go

harishgg1 · July 9, 2019, 12:36pm

hi basit

i tried it and put solution !!!

is this what your are looking FOR ??

please dont mind my asking

basit0079 · July 9, 2019, 12:41pm

Many Many Thanks, But just add one entery for different bank in income it will not shows the result. Like HSBC Bank 1400 add in income its not showing.

It showing below result.

(No column name)	bankname	totalincome	totalexpense	Balance
Normal	HDFC Bank	337946.45	17349.69	320596.76
Inter	HDFC Bank	3442393.15	17349.69	3425043.46
Inter	HDFC Bank	3105846.70	17349.69	3088497.01

below is the income table data.

IncomeNo	IncomeDate	IncomeDepositeinBank	IncomeAmount
11111	2019-07-09	HDFC Bank	102234.54
11111	2019-07-10	HDFC Bank	1266.80
11111	2019-07-11	HDFC Bank	234445.11
11111	2019-07-11	HSBC	1400.00

harishgg1 · July 9, 2019, 12:55pm

i dont understand what you are saying BASIT

your english is not making any sense to me !!!

please help me understand
giving you the solution is very very easy
understanding you is the only HARD part

harishgg1 · July 9, 2019, 1:32pm

hi basit

i tried to understand what you are saying
hope it helps

is this the solution ???

SQL for above solution

;WITH cteincome 
     AS (SELECT incomedepositeinbank AS bankname, 
                Sum(incomeamount)    AS TotalIncome 
         FROM   income 
         GROUP  BY incomedepositeinbank), 
     cteexpense 
     AS (SELECT expenseissuefrombank AS bankname, 
                Sum(expenseamount)   AS TotalExpense 
         FROM   expense 
         GROUP  BY expenseissuefrombank), 
     cteinter 
     AS (SELECT bankinterfrombank, 
                bankintertobank, 
                Sum(bankinteramount) AS TotalAmount 
         FROM   bankintertransfer 
         GROUP  BY bankinterfrombank, 
                   bankintertobank) 
SELECT 'Normal',a.bankname, 
       a.totalincome, 
       b.totalexpense, 
       a.totalincome - b.totalexpense AS Balance 
FROM   cteincome a 
       JOIN cteexpense b 
         ON a.bankname = b.bankname 
UNION ALL 
SELECT 'Inter',c.bankname, 
       c.totalincome + a.totalamount                  AS totalincome, 
       b.totalexpense, 
       c.totalincome + a.totalamount - b.totalexpense AS Balance 
FROM   cteinter a 
       JOIN cteexpense b 
         ON a.bankintertobank = b.bankname 
       JOIN cteincome c 
         ON a.bankintertobank = b.bankname 

go

basit0079 · July 9, 2019, 2:24pm

Really really sorry. I will explain with Data.

Example Income Table is having below Data.

IncomeNo	IncomeDate	IncomeDepositeinBank	IncomeAmount
11111	2019-07-09	HDFC	100.00
11111	2019-07-10	HDFC	200.00
11111	2019-07-11	HSBC	100.00

and Expense table is below data

ExpenseNo	ExpenseDate	ExpenseSupplier	ExpenseAmount	ExpenseIssueFromBank
1234	2019-04-10	ok supplies	50.00	HDFC
1234	2019-04-11	ok supplies	50.00	HDFC
1234	2019-04-12	ok supplies	10.00	HSBC

Note that BankInterTransfer is empty.

Then Result must be.

bankname	totalincome	totalexpense	Balance
HDFC	300	100	200
HSBC	100	10	90

Now im transfer Money from HDFC to HSBC 10

BankInterNo	BankInterDate	BankInterFromBank	BankInterToBank	BankInterAmount	BankInterRemark
1	2019-01-01	HDFC	HSBC	10.00	-

Note that the income & expense table are having same data.

then the result will be below.

bankname	totalincome	totalexpense	Balance
HDFC	300	100	190
HSBC	100	10	100

Please let me know if this clear for you. and Many thanks once again.

Thanks
Basit.

harishgg1 · July 9, 2019, 3:08pm

hi basit

based on what i understood

i tried

please check ...

drop create data ..

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',100.00
insert into #income select 11111,'2019-07-10','HDFC',200.00
insert into #income select 11111,'2019-07-11','HSBC',100.00
go 

select * from #income 
go 

drop table #expenses  
go 

create table #expenses 
(
ExpenseNo	int,
ExpenseDate	date,
ExpenseSupplier	varchar(100),
ExpenseAmount	decimal(10,2),
ExpenseIssueFromBank varchar(10)
)
go 

insert into #expenses  select 1234,'2019-04-10','ok supplies',50.00,'HDFC'
insert into #expenses  select 1234,'2019-04-11','ok supplies',50.00,'HDFC'
insert into #expenses  select 1234,'2019-04-12','ok supplies',10.00,'HSBC'
go 

select * from #expenses  
go 
 
drop table #bankinter 
go 

create table #bankinter
(
BankInterNo	int,
BankInterDate	date,
BankInterFromBank	varchar(100),
BankInterToBank	varchar(100),
BankInterAmount	decimal(10,2),
BankInterRemark varchar(100)
)
go 

insert into #bankinter select 1,'2019-01-01','HDFC','HSBC',10.00,'-'
go 

select * from #bankinter 
go

SQL..

;WITH cteincome 
     AS (SELECT incomedepositeinbank, 
                Sum(incomeamount) AS totalincome 
         FROM   #income 
         GROUP  BY incomedepositeinbank), 
     cteexpenses 
     AS (SELECT expenseissuefrombank, 
                Sum(expenseamount) AS TotalExpense 
         FROM   #expenses 
         GROUP  BY expenseissuefrombank), 
     cteinter 
     AS (SELECT * 
         FROM   #bankinter) 
SELECT a.incomedepositeinbank, 
       a.totalincome, 
       b.totalexpense, 
       a.totalincome - b.totalexpense - c.bankinteramount 
FROM   cteincome a 
       JOIN cteexpenses b 
         ON a.incomedepositeinbank = b.expenseissuefrombank 
       JOIN cteinter c 
         ON a.incomedepositeinbank = c.bankinterfrombank 
UNION ALL 
SELECT a.incomedepositeinbank, 
       a.totalincome, 
       b.totalexpense, 
       a.totalincome - b.totalexpense + c.bankinteramount 
FROM   cteincome a 
       JOIN cteexpenses b 
         ON a.incomedepositeinbank = b.expenseissuefrombank 
       JOIN cteinter c 
         ON b.expenseissuefrombank = c.bankintertobank 

go

Result

harishgg1 · July 9, 2019, 3:51pm

hi basit

i have given you the solution based on what i understood
hope it helps

sorry please dont mind my asking
which country are you from ? and doing SQL from ?
thanks