Accounting problem in SQL

User 13974416

Hello everyone .
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

Mycroft Holmes

What do you mean by cover

Member 14006806 wrote:

Creditor documents cover debtor documents.

Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP

CHill60

I guessing that by

Quote:

Creditor documents cover debtor documents.

you are implying that there may be more than one credit document to cover debt documents. You are essentially trying to keep a running total (hint - good thing to google for). But first you have to get your data into a useable form. In the example below I am going to DocID to indicate the order in which the documents were received because the data you have in columns docDate is not a date and I can't use it in an order by clause. The first step is to get all of the documents into a single result set - I'm not sure why you have columns ISDeb/IsDebit when you can tell what they are by what table they are in. Personally, I would have had one transaction table with all types of transaction in the one place, with debit values negative and credit values positive. But as you have a different model, you will need to start with something like this ...

select personID, DocDate, DocID, (-1) * Fee As Fee, IsDebit
from #debit
union all
select personID, DocDate, DocID, Fee, ISDeb
from #credit

There are several ways to calculate a running total e.g. you could adapt one of the solutions from this post[^]

;with combine as
(
select personID, DocDate, DocID, (-1) * Fee As Fee, IsDebit
from #debit
union all
select personID, DocDate, DocID, Fee, ISDeb
from #credit
)
,CTE
as
(
select T.personID, T.DocDate, T.DocID, T.Fee, T.Fee as running_total
from combine as T
where T.DocID = 1
union all
select T.personID, T.DocDate, T.DocID, T.Fee, T.Fee + C.running_total as running_total
from CTE as C
inner join combine as T on T.DocID = C.DocID + 1
)
select C.PersonID, C.DocDate, C.DocID, C.Fee, C.running_total
from CTE as C

Which gave results

PersonID DocDate DocID Fee running_total
88 2/14 1 -5 -5
88 2/15 2 -5 -10
88 2/16 3 3 -7
88 2/17 4 7 0

This strikes me very much as homework, so just be aware that your tutor probably knows about this site as well That still doesn't get the results you want - you need to start looking at PIVOT to get credit fee and debit fee on the same row - but for that you are go