sqlsql-serveraccountingissuu

Accounting Calculate Debit credit in SQL(ssms)


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

Solution

  • 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.