CODEWITHSUNDEEP
c#sqlt-sql
franchesco totti
franchesco totti

Reputation: 644

subtract debit and credit column

I wrote following view in sql

SELECT     TOP (100) PERCENT Accounting.TopicA.CodeA, SUM(Accounting.DocumentDetail.Debit) AS DEB, SUM(Accounting.DocumentDetail.Credit) AS CRED, 
                      Accounting.TopicA.Description
FROM         Accounting.TopicA INNER JOIN
                      Accounting.DocumentDetail ON Accounting.TopicA.CodeA = SUBSTRING(Accounting.DocumentDetail.Topic, 1, 2)
GROUP BY Accounting.TopicA.CodeA, Accounting.TopicA.Description
ORDER BY Accounting.TopicA.CodeA

And the result is

codea |Description | DEB | CRED |
1      Bank        | 100 | 30   |
2      Cash        | 40  | 70   |
.
.
.

now i need to add two more column to subtract DEB and CRED ,like when subtraction is positive then put the result in POS column else NEG column like below 

codea |Description | DEB | CRED |  NEG | POS |
1      Bank        | 100 | 30   | 70   | 0   |
2      Cash        | 40  | 70   | 0    | 30  |
.
.
.

Upvotes: 0

Views: 878

Answers (3)

Servy
Servy

Reputation: 203842

For the negative column you can use:

ABS(Min(0, theSubtraction))

for the positive column you can use:

Max(0, theSubtraction)

Upvotes: 0

criticalfix
criticalfix

Reputation: 2870

Try something like this:

SELECT TOP (100) PERCENT Accounting.TopicA.CodeA,
SUM(Accounting.DocumentDetail.Debit) AS DEB,
SUM(Accounting.DocumentDetail.Credit) AS CRED,
CASE WHEN SUM(Accounting.DocumentDetail.Credit) - SUM(Accounting.DocumentDetail.Debit) < 0
THEN SUM(Accounting.DocumentDetail.Debit) - SUM(Accounting.DocumentDetail.Credit)
ELSE 0 END AS NEG,
CASE WHEN SUM(Accounting.DocumentDetail.Credit) - SUM(Accounting.DocumentDetail.Debit) > 0
THEN SUM(Accounting.DocumentDetail.Credit) - SUM(Accounting.DocumentDetail.Debit)
ELSE 0 AS POS,
Accounting.TopicA.Description
FROM Accounting.TopicA 
INNER JOIN Accounting.DocumentDetail 
ON Accounting.TopicA.CodeA = SUBSTRING(Accounting.DocumentDetail.Topic, 1, 2)
GROUP BY Accounting.TopicA.CodeA, Accounting.TopicA.Description
ORDER BY Accounting.TopicA.CodeA

Upvotes: 3

Emmanuel Capelle
Emmanuel Capelle

Reputation: 44

I have the idea to use a CASE statement to do this.

Here's a link to sql server's CASE documentation : http://msdn.microsoft.com/en-us/library/ms181765.aspx
I'm just guessing you're using sql server as the question is stamped C#, though it should be compatible with mysql, oracle,... too.

The idea is to use this CASE to know when to put 0 either in the NEG or POS column, and where to put the actual result.

Note: Do you really have to have the NEG and POS columns? Don't you just want to create a "result" column instead?

Upvotes: 0

Related Questions