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
Sample data
Using an ISO date format to avoid any confusion.
The docdate
and isdebit
columns will not be used in the solution...
docdate
under the assumptions that the values are incremental and that it is allow to deposit a credit fee before any debit fee.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:
cte_debit
).cte_credit
).select * from cte_debit
)cross apply
(cc1
). This contains the docid
of the first document that applies to the debit document.cross apply
(cc2
). This contains the docid
of the last document that applies to the debit document.join cte_credit cc on cc.docid >= cc1.docid and cc.docid <= cc2.docid
).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.