Querying Journal Entries - Debit/Credit mismatches?

I'm working through an export from Simply Accounting into a BI Package.

I have a basic query written that flushes out the journal entry - but the debits and credits for the journal entry are not 'netting' zero because of the positive/negative sign...

First image shows an export based on a SQL Query out of Simply accounting, showing a line for each line entry in the journal. You will notice that the debit/credit amounts differ types (debits vs credits) than those shown in the Simply Screen shot showing the Purchase Transaction Detail.

Results of my flushed out query:

Showing 475.96 as a debit..

Showing 4007.99, 4483.95 as a credit...

View from Simply: Purchase Transaction Detail

Showing 475.96 and 4007.99 as debits..

Showing 4483.95 s a credit...

Is there a simply way to fix this in my query? How is the data stored in the tables vs how it is displayed in the Simply Screen shot???

Any help you can provide would be appreciated.

Thanks.

Mike

Parents
  • Basically the dollars are stored so that positive numbers increase the balance of the account and negative numbers decrease it. So you will have to add logic to swap the sign for debits in credit accounts.

    Depending on your SQL version and how much access you have to the statement to make changes, you can add a CASE statement that will switch it for any account that is a credit account. Of course you will have to swap the sign on credits in credit accounts as well if you show the negative signs. If you don't have that kind of access to the SQL level, you will have to handle it in the post-SQL processing stage.
  • Are you able to provide any example code?
  • CASE
    WHEN tactrang.nAcctClass in (2,3,4) then (tJEntLYA.dAmount * - 1)
    ELSE
    tJEntLYA.dAmount
    END AS 'Amount'

    ... This is the case I built, look about right?
  • Mike

    What I see in this statement is that you wish to turn any debit into a positive number and any credit into a negative number for any liability, equity or revenue account in last year's journal entries.

    If that is your goal, it should work just fine.
  • Hi Richard - Using the info you provided, I was able to create a proper export.

    Thank you!!
  • Hey Richard - 4 years ago we chatted about this!  Hoping you can provide some additional insight.

    For the project I was working on previously, we only really used the P&L piece for the BI Tool. We're now diving into the Balance Sheet items - which in some cases, isn't lining up how we'd like based on the code I wrote below:

    CASE
    WHEN tactrang.nAcctClass in (2,3,4) then (tJEntLYA.dAmount * - 1)
    ELSE
    tJEntLYA.dAmount
    END AS 'Amount'

    Essentially, the code above is always flipping the sign when the amount is from a Credit Account.

    Using this logic on one of our transactions is shown below:

    Out of Sage/50/Can:

    Result Set out of MySQL

    ** [OUT] is the result of my calculation above.

    When we import this transaction into our BI tool - the balances go out of wack.

    The assumption when going into the BI tool is:

    Assets - Debits positives, Credits Negative.
    Liabilites – Debits negatives, Credits positives.
    Revenues – Debits negatives, Credits positives
    Expenses – Debits positives, credits negatives.
    Equity – Debits negatives, credits positives

    It seems the sign for 12020 is good... its an asset account, performing a debit - should be positive.

    The sign for the 49000 seems off - its a revenue account, performing a credit - should be positive - but the logic is telling us to flip the sign...

    We're performing other imports related to revenue, and it appears OK - but I want to start back at the source-system and really be sure I'm thinking of this correctly.

    Unfortunately, I don't have a "great" accounting brain.

    At a quick glance, do you see what I may be doing wrong?

    Very much appreciate your contribution to the community!

    Thank you.

    M.

  • Sage 50 stores all credit balances in accounts in nAcctClass 2, 3 and 4 as positive numbers.

    Your SQL statement turns them into negatives.

    Revenues – Debits negatives, Credits positives

    If your BI Tool is expecting this, then why did you write the SQL statement to convert all credits to negatives?  I think that is where you need to start looking at your logic.  nAcctClass 1, 2 and 3 are the Balance sheet Assets, Liabilities and Equities respectively regardless of account number.

    BTW you don't have to link to other websites to upload images, you can just upload them to this site using Insert and select Upload when it is available (it's not always there, like today but usually it is).

Reply
  • Sage 50 stores all credit balances in accounts in nAcctClass 2, 3 and 4 as positive numbers.

    Your SQL statement turns them into negatives.

    Revenues – Debits negatives, Credits positives

    If your BI Tool is expecting this, then why did you write the SQL statement to convert all credits to negatives?  I think that is where you need to start looking at your logic.  nAcctClass 1, 2 and 3 are the Balance sheet Assets, Liabilities and Equities respectively regardless of account number.

    BTW you don't have to link to other websites to upload images, you can just upload them to this site using Insert and select Upload when it is available (it's not always there, like today but usually it is).

Children
No Data