Collect data From Table
-------------------------------------
SELECT * FROM tb_Debit
select * from tb_credit
----------------------------------------
work start from here
-------------------------------------
create table #t
(
MobileNo Varchar(15),
Credit float,
Debit float,
TrnDate Datetime
)
insert into #t
(
MobileNo,Credit,Debit,TrnDate
)
Select MobileNo,credit,debit,trnDate
from
(
select MobileNo,credit,0 as debit,trnDate from tb_credit
union all
select MobileNo,0 as credit,debit,trnDate from tb_Debit
)y
order by trnDate asc
--select * from #t
with #ledger as
(
select
MobileNo,
TrnDate,
Credit,
Debit,
ROW_NUMBER()over(ORDER by trndate)as row
from #t
)
SELECT
L1.MobileNo,
L1.TrnDate,
(ISNULL(MAX(L1.Credit),0)+ ISNULL(SUM(L2.Credit),0)- ISNULL(SUM(L2.Debit),0)) AS Opening,
L1.Credit As Credit,
L1.Debit As Debit,
(ISNULL( L1.Credit,0) + ISNULL(SUM(L2.Credit),0)- ISNULL(SUM(L2.Debit),0)- ISNULL((L1.Debit),0)) As closing
FROM #ledger L1
LEFT JOIN #ledger L2
ON L1.row>L2.row
GROUP BY L1.MobileNo,L1.TrnDate,L1.Credit,L1.Debit
: It is the One Of the Best Solution For your question