SQLTeam.com | Weblogs | Forums

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.

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

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

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.

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

image

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.

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

image

hi basit

i tried it and put solution !!!

is this what your are looking FOR ??

please dont mind my asking
:slight_smile:

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

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

:slight_smile:
:slight_smile:

hi basit

i tried to understand what you are saying
hope it helps
:slight_smile: :slight_smile:

is this the solution ???
image

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

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.

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

hi basit

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

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