Page History

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


--Out of balance by period!
SELECT cAuditNumber, * FROM PostGL
    WHERE cAuditNumber IN (
        SELECT cAuditNumber FROM PostGL GROUP BY cAuditNumber, Period HAVING ABS(ROUND(SUM(Debit-Credit),2)) > 0.01
    )
   AND Period > 0 --excludes CB unreconciled tx from prior years.
    ORDER BY PostGL.cAuditNumber

SELECT *,DiffDebit = t1.Debit - t2.fActualDebit , DiffCredit = t1.Credit - t2.fActualCredit, TotalDiff = ABS(t1.Debit - t2.fActualDebit) + ABS(t1.Credit - t2.fActualCredit)
	FROM (
		SELECT AccountLink, Period, Project, SUM(ROUND(Debit,2)) as Debit, SUM(ROUND(Credit,2)) as Credit FROM PostGL GROUP BY AccountLink, Period, Project ) t1
		LEFT JOIN (SELECT AB.iAccBlncAccountID, AB.iAccBlncProjectID, AB.iAccBlncPeriodID, AB.fActualDebit, AB.fActualCredit FROM _etblAccBlnc AB) t2 ON t1.AccountLink = t2.iAccBlncAccountID AND t1.Project = t2.iAccBlncProjectID AND t1.Period = t2.iAccBlncPeriodID
		WHERE 
			ABS(t1.Debit - t2.fActualDebit) > 0.049 or ABS(t1.Credit - t2.fActualCredit)> 0.049
		order by TotalDiff desc