# Thread: Negative debits or credits

1. Registered User
Join Date
Feb 2013
Posts
46

## Unanswered: Negative debits or credits

Hi everyone,
I have a table with debits and credits columns
Credit Debit
-76 00
00 80
90 -30
I am transferring the debits and the credits in to one column in another table
Type Amount
C -76
D 80
C 90
D -30
And I am facing two problems one is the negative numbers dont transfer and also when I have debit and credit on the same line they are not transferring.

Select Case When isNull(A.[Debit], 0) > 0 And IsNull(A.[Credit], 0) = 0 Then 'D' Else 'C' End,
Case When isNull(A.[Debit], 0) > 0 And IsNull(A.[Credit], 0) = 0 Then Round(A.[Debit],2) Else Round(A.[Credit],2) End
FROM Tble A

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
what do you think your case statement is representing
Id suggest stepping through each element and make certain it matches what you say you want to happen with your data

logically a negative credit is a debit, and similarly a negative debit is a credit

either your data is at fault or your case expression is at fault, the two are mutually incompatible

3. Registered User
Join Date
Nov 2004
Posts
1,428
Start by breaking up your INSERT statement in two INSERT statements, to translate

Credit Debit
90 -30

to two INSERTS.

If you have a column for the Credits and one for the Debits, I would expect them both to be positive numbers. What is negative Credit?

I don't know what logic is behind values in those two columns.
Identify the logic behind
- a positive value in the Credit column
- a negative value in the Credit column
- a positive value in the Debit column
- a negative value in the Debit column
and how each should it be translated into your new table.

Write one INSERT statement for each case, that will eliminate an error that you are introducing by doing it all in one statement. You must at least have two INSERT statements, one for the Credit column data migration and one for the Debit column data migration.

Play safe, do it in four INSERT statements. It will be a lot easier to test and debug. With financial data you don't want to mess around.

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
While mathematically both a credit and a negative debit have the same effect on the "bottom line" balance of a given account, from an accounting (GAAP) perspective they are quite different. I don't know of a double-entry accounting system that doesn't store debits and credits separately and allow numeric signs on both of them.

For the asset, expenses, and drawing categories of accounts, debit increases the net value of the account. For accounts in the liabilities, revenue, and equity categories, a debit decreases the net value of the account.

Most folks learn this stuff in high school or secondary school, but promptly forget that they ever saw it. Unless you're an accountant or work with them regularly this probably won't make much sense.

This is also why accounting isn't for the faint of heart... The process is complicated, but that complication is necessary in order to ensure that the "book value" is simple and reliable for an audited set of statements. The world's financial systems rest upon that assumption.

M1N hasn't provided enough information for me to be comfortable answering the question. Based on what I can see from the initial post, the answer will be meaningless but I don't know enough about their problem to understand exactly what may go wrong.

-PatP

5. Registered User
Join Date
Feb 2013
Posts
46
Thank you for the suggestions Wim, this is an excel spreadsheet that is imported and it comes with negative numbers, I didn't think of doing two separate inserts.

6. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483

Code:
```SELECT b.type
, CASE b.type
WHEN 'C' THEN
a.credit
ELSE a.debit
END  AS amount
FROM  Tble AS a
INNER JOIN
(VALUES ('C') , ('D') ) AS b(type)
ON   b.type = 'C' AND a.credit <> 0
OR  b.type = 'D' AND a.debit  <> 0
;```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•