...
SQL - View transactions out of balance by audit number
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT * FROM PostGL
WHERE cAuditNumber IN (
SELECT cAuditNumber FROM PostGL GROUP BY cAuditNumber HAVING ROUND(SUM(Debit-Credit),2) <> 0
)
--And now transactions out by more than 1 cent.
SELECT cAuditNumber, * FROM PostGL
WHERE cAuditNumber IN (
SELECT cAuditNumber FROM PostGL GROUP BY cAuditNumber HAVING ABS(ROUND(SUM(Debit-Credit),2)) > 0.01
)
AND Period > 0 --excludes CB unreconciled tx from prior years.
ORDER BY PostGL.cAuditNumber |
SQL - Amounts out of balance by audit number
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT cAuditNumber, ROUND(SUM(Debit-Credit),2) FROM PostGL GROUP BY cAuditNumber HAVING ROUND(SUM(Debit-Credit),2) <> 0 |
SQL - Update / View transactions with more than 2 decimals in Evolution
| Code Block | |||||
|---|---|---|---|---|---|
|
| ||||
select * from PostGL where ROUND(Debit+Credit,4) <> Round(debit+credit,2) update PostGL set debit = round(debit,2), credit = round(credit,2) where ROUND(Debit+Credit,4) <> Round(debit+credit,2) |