Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013

    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

    thank you for your hep!

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    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

    alternativley revisit 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
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    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.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2013
    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. #6
    Join Date
    Feb 2008
    How about this?

    SELECT b.type
         , CASE b.type
           WHEN 'C' THEN
           ELSE a.debit
           END  AS amount
     FROM  Tble AS a
           (VALUES ('C') , ('D') ) AS b(type)
      ON   b.type = 'C' AND <> 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