Querying Journal Entries - Debit/Credit mismatches?

SUGGESTED

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
  • 0
    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.
  • 0 in reply to Richard S. Ridings
    Thanks Richard.

    I'm well versed in SQL - writing my queries in a MySQL client for testing purposes.

    I should have no problems with the case statement. Is there a way to test an account type to see whether its a debit/credit account?
Reply Children
No Data