Accounting Calculate Debit credit in SQL(ssms)

Question

I have an accounting calculation problem. I want to write it with SQL Query (in ssms). I have two groups of documents related to one person (creditor and debtor) Creditor documents cover debtor documents. Consider the following example: (How can the result be achieved?)

USE [master]
GO

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

Answer 1

Sample data

Using an ISO date format to avoid any confusion.

The docdate and isdebit columns will not be used in the solution...

1. I ignored the docdate under the assumptions that the values are incremental and that it is allow to deposit a credit fee before any debit fee.
2. The isdebit flag seems redundant if you are going to store debit and credit transactions in separate tables anyway.

Updated sample 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);

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);

Solution

Couple steps here:

1. Construct a rolling sum for the debit fees. Done with a first common table expression (cte_debit).
2. Construct a rolling sum for the credit fees. Done with a second common table expression (cte_credit).
3. Take all debit info (select * from cte_debit)
4. Find the first credit info that applies to the current debit info. Done with a first cross apply (cc1). This contains the docid of the first document that applies to the debit document.
5. Find the last credit info that applies to the current debit info. Done with a second cross apply (cc2). This contains the docid of the last document that applies to the debit document.
6. Find all credit info that applies to the current debit info by selecting all documents between the first and last applicable document (join cte_credit cc on cc.docid >= cc1.docid and cc.docid <= cc2.docid).
7. Combine the rolling sum numbers to calculate the remaining credit fees (cc.credit_sum - cd.debit_sum). Use a case expression to filter out negative values.

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,
       cc.docid as cre_docid,
       cc.fee as cre_fee,
       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
              order by cc1.credit_sum desc ) cc1
cross apply ( select top 1 cc2.docid, cc2.credit_sum
              from cte_credit cc2
              where cc2.personid = cd.personid
                and cc2.credit_sum >= cd.debit_sum
              order by cc2.credit_sum desc ) cc2
join cte_credit cc
  on  cc.personid = cd.personid
  and cc.docid >= cc1.docid
  and cc.docid <= cc2.docid
order by cd.personid,
         cd.docid,
         cc.docid;

Result

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

Fiddle to see things in action. This also contains the intermediate CTE results and some commented helper columns that can be uncommented to help to further understand the solution.