Styjun

I am developing a database application for a small electronics business. I need a SQL query which takes the closing balance of previous day as an opening balance of current day. I have following data tables

Expensis

ExpenseID Date Expense 

1 2019-03-01 2,000
2 2019-03-02 1,000
3 2019-03-03 500

Income

IncomeID Date Income

1 2019-03-01 10,000
2 2019-03-02 13,000
3 2019-03-03 10,000

Required result

Date Opening Balance Income Expense Closing Balance

2019-03-01 0 10,000 2,000 8,000
2019-03-02 8,000 13,000 1,000 20,000 
2019-03-03 20,000 10,000 5,00 29,500 

You can use sum aggregation function recursively ( lag window analytic function cannot be used for sql server 2008 )

with Expensis( ExpenseID, Date, Expense ) as
(
 select 1, '2019-03-01', 2000 union all
 select 2, '2019-03-02', 1000 union all
 select 3, '2019-03-03', 500 
 ), Income( IncomeID, Date, Income ) as
 (
 select 1, '2019-03-01', 10000 union all
 select 2, '2019-03-02', 13000 union all
 select 3, '2019-03-03', 10000 
 ), t as
 (
 select i.date, 
 i.income,
 e.expense,
 sum(i.income-e.expense) over (order by i.date) as closing_balance 
 from income i
 join expensis e on e.date = i.date
 )
 select date, 
 ( closing_balance - income + expense ) as opening_balance,
 income, expense, closing_balance
 from t;

date opening balance income expense closing balance
---------- --------------- ------ ------- ---------------
2019-03-01 0 10000 2000 8000
2019-03-02 8000 13000 1000 20000
2019-03-03 20000 10000 500 29500

Demo

Here is one way you could do it
You have to valuate income and expenses differently
WITH INCOME AS
(
SELECT '2018-01-05' AS DT, 200 AS INC, 1 AS TP
UNION ALL
SELECT '2018-01-06' AS DT, 300 AS INC, 1 AS TP
UNION ALL
SELECT '2018-01-07' AS DT, 400 AS INC, 1 AS TP
)

, EXPENSES AS
 (
SELECT '2018-01-05' AS DT, -100 AS EXPS, 2 AS TP
UNION ALL
SELECT '2018-01-06' AS DT, -500 AS EXPS, 2 AS TP
UNION ALL
SELECT '2018-01-07' AS DT, -30 AS EXPS, 2 AS TP
)

, UN AS
(
SELECT * FROM INCOME
UNION ALL
SELECT * FROM EXPENSES
)

SELECT *, [1]+[2] AS END_BALANCE FROM UN

PIVOT
(
 SUM(INC)
 FOR TP IN ([1],[2]) 
) AS P