Click here to Skip to main content
14,971,029 members
Home / Discussions / Database
   

Database

 
AnswerRe: SQL statements Pin
SeanChupas27-May-21 9:02
MemberSeanChupas27-May-21 9:02 
QuestionRe: SQL statements Pin
David Crow27-May-21 9:12
MemberDavid Crow27-May-21 9:12 
AnswerRe: SQL statements Pin
Mycroft Holmes27-May-21 12:19
professionalMycroft Holmes27-May-21 12:19 
QuestionSQL query dilemma Pin
David Crow20-May-21 10:31
MemberDavid Crow20-May-21 10:31 
AnswerRe: SQL query dilemma Pin
Mycroft Holmes20-May-21 13:05
professionalMycroft Holmes20-May-21 13:05 
QuestionAccounting problem in SQL Pin
Member 1400680615-May-21 23:23
MemberMember 1400680615-May-21 23:23 
AnswerRe: Accounting problem in SQL Pin
Mycroft Holmes16-May-21 12:17
professionalMycroft Holmes16-May-21 12:17 
AnswerRe: Accounting problem in SQL Pin
CHill6019-May-21 2:55
mveCHill6019-May-21 2:55 
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 ...
SQL
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[^]
SQL
;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 going to need a way of linking the two together i.e. this credit value is to cover debit X
QuestionSQL Server Execution Timeout Expired Pin
idkd12-May-21 23:00
Memberidkd12-May-21 23:00 
AnswerRe: SQL Server Execution Timeout Expired Pin
SeanChupas13-May-21 1:49
MemberSeanChupas13-May-21 1:49 
QuestionIn-memory database with acid transactions and high availability Pin
Mathieu Seillier12-May-21 2:54
MemberMathieu Seillier12-May-21 2:54 
AnswerRe: In-memory database with acid transactions and high availability Pin
Mycroft Holmes12-May-21 13:00
professionalMycroft Holmes12-May-21 13:00 
AnswerRe: In-memory database with acid transactions and high availability Pin
Richard MacCutchan12-May-21 21:08
mveRichard MacCutchan12-May-21 21:08 
QuestionSQL Server round trip issue between server and client pc Pin
Mou_kol1-May-21 6:35
MemberMou_kol1-May-21 6:35 
AnswerRe: SQL Server round trip issue between server and client pc Pin
Mycroft Holmes1-May-21 11:57
professionalMycroft Holmes1-May-21 11:57 
AnswerRe: SQL Server round trip issue between server and client pc Pin
Gerry Schmitz1-May-21 17:12
mveGerry Schmitz1-May-21 17:12 
GeneralRe: SQL Server round trip issue between server and client pc Pin
Mycroft Holmes2-May-21 12:24
professionalMycroft Holmes2-May-21 12:24 
GeneralRe: SQL Server round trip issue between server and client pc Pin
Gerry Schmitz2-May-21 18:53
mveGerry Schmitz2-May-21 18:53 
QuestionSqlServer (and ADO.NET) mystery... Pin
Super Lloyd27-Apr-21 19:19
MemberSuper Lloyd27-Apr-21 19:19 
AnswerRe: SqlServer (and ADO.NET) mystery... Pin
Richard Deeming27-Apr-21 22:11
mveRichard Deeming27-Apr-21 22:11 
GeneralRe: SqlServer (and ADO.NET) mystery... Pin
Super Lloyd27-Apr-21 22:42
MemberSuper Lloyd27-Apr-21 22:42 
AnswerRe: SqlServer (and ADO.NET) mystery... Pin
Mycroft Holmes28-Apr-21 11:46
professionalMycroft Holmes28-Apr-21 11:46 
QuestionInterval schedule Pin
Member 1462398929-Apr-21 2:56
MemberMember 1462398929-Apr-21 2:56 
AnswerRe: Interval schedule Pin
Victor Nijegorodov27-Apr-21 20:45
MemberVictor Nijegorodov27-Apr-21 20:45 
AnswerRe: Interval schedule Pin
Richard Deeming27-Apr-21 22:08
mveRichard Deeming27-Apr-21 22:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.