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
