SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--https://stackoverflow.com/questions/67555186/accounting-calculate-debit-credit-in-sqlssms

DROP TABLE IF EXISTS #credit/*creditor=0*/,#debit/*Debtor=1*/

SELECT *
INTO #debit
FROM    (values 
        (88,'2/14',1,5,1),(88,'2/15',2,5,1)
        )A (personID,DocDate,DocID,Fee,IsDebit)

SELECT *
INTO #credit
FROM    (values 
        (88,'2/16',3,3,0),(88,'2/17',4,7,0)
        )A (personID,DocDate,DocID,Fee,ISDeb)
SELECT * FROM #credit
SELECT * FROM #debit


--result:
;WITH res AS
(
    SELECT 88 AS personID ,1 deb_DocID ,5 deb_Fee , 3 Cre_DocID ,3 Cre_Fee, 0 remain_Cre_Fee
    UNION
    SELECT 88 AS personID ,1 deb_DocID ,5 deb_Fee , 4 Cre_DocID ,7 Cre_Fee, 5 remain_Cre_Fee
    UNION
    SELECT 88 AS personID ,2 deb_DocID ,5 deb_Fee , 4 Cre_DocID ,7 Cre_Fee, 0 remain_Cre_Fee
)

SELECT *
FROM res



personID
DocDate
DocID
Fee
ISDeb


88
2/16
3
3
0

88
2/17
4
7
0




personID
DocDate
DocID
Fee
IsDebit


88
2/14
1
5
1

88
2/15
2
5
1




personID
deb_DocID
deb_Fee
Cre_DocID
Cre_Fee
remain_Cre_Fee


88
1
5
3
3
0

88
1
5
4
7
5

88
2
5
4
7
0


-- credit data
create table debit
(
  personid int,
  docdate date,
  docid int,
  fee int,
  isdebit bit
);

insert into debit (personid, docdate, docid, fee, isdebit) values
(88, '2021-02-14', 1, 5, 1),
(88, '2021-02-15', 2, 5, 1);

select * from debit;



personid
docdate
docid
fee
isdebit


88
2021-02-14
1
5
True

88
2021-02-15
2
5
True


-- debit data
create table credit
(
  personid int,
  docdate date,
  docid int,
  fee int,
  isdebit bit
);

insert into credit (personid, docdate, docid, fee, isdebit) values
(88, '2021-02-16', 3, 3, 0),
(88, '2021-02-17', 4, 7, 0);

select * from credit;



personid
docdate
docid
fee
isdebit


88
2021-02-16
3
3
False

88
2021-02-17
4
7
False


-- rolling sum credit
select d.personid,
       d.docid,
       d.fee,
       sum(d.fee) over(order by d.docid rows between unbounded preceding and current row) as debit_sum
from debit d
order by d.docid;

-- rolling sum debit
select c.personid,
       c.docid,
       c.fee,
       sum(c.fee) over(order by c.docid rows between unbounded preceding and current row) as credit_sum
from credit c
order by c.docid;



personid
docid
fee
debit_sum


88
1
5
5

88
2
5
10




personid
docid
fee
credit_sum


88
3
3
3

88
4
7
10


-- full solution
with cte_debit as
(
  select d.personid,
         d.docid,
         d.fee,
         sum(d.fee) over(order by d.docid rows between unbounded preceding and current row) as debit_sum
  from debit d
),
cte_credit as
(
  select c.personid,
         c.docid,
         c.fee,
         sum(c.fee) over(order by c.docid rows between unbounded preceding and current row) as credit_sum
  from credit c
)
select cd.personid,
       cd.docid as deb_docid,
       cd.fee as deb_fee,
       --cd.debit_sum as deb_total,
       --cc1.credit_sum as cre_total_from,
       --cc2.credit_sum as cre_total_to,
       cc.docid as cre_docid,
       cc.fee as cre_fee,
       --cc.credit_sum - cd.debit_sum as cre_fee_open,
       case
         when cc.credit_sum - cd.debit_sum >= 0
         then cc.credit_sum - cd.debit_sum
         else 0
       end as cre_fee_remaining
from cte_debit cd
cross apply ( select top 1 cc1.docid, cc1.credit_sum
              from cte_credit cc1
              where cc1.personid = cd.personid
                and cc1.credit_sum <= cd.debit_sum


personid
deb_docid
deb_fee
cre_docid
cre_fee
cre_fee_remaining


88
1
5
3
3
0

88
1
5
4
7
5

88
2
5
4
7
0