/*IF WE FIND A DIFFERENCE WE CAN THEN DRILL BACK INTO THE DETAILS FOR THIS DATE AND SEE WHERE THE DIFFERENCE IS.*/
DECLARE @DATE DATETIME
SELECT @DATE = '2009.04.29'

SELECT A.*, B.*, (A.HB_TOTAL - B.GL_TOTAL) AS DIFF FROM (SELECT JRNENTRY, SUM(EXTDCOST) AS HB_TOTAL FROM SEE30303 WHERE GLPOSTDT = @DATE GROUP BY JRNENTRY) A
/*CHANGE THE JOIN FROM LEFT TO RIGHT IN ORDER TO SEE THE DIFFERENCES BOTH WAYS.*/
LEFT JOIN (SELECT A.JRNENTRY, SUM(A.TOTAL) AS GL_TOTAL FROM (
	SELECT JRNENTRY, SUM(DEBITAMT - CRDTAMNT) AS TOTAL FROM GL20000
	WHERE TRXDATE = @DATE AND ACTINDX IN (SELECT ACTINDX FROM GL00105 WHERE ACTNUMBR_3 = '1400')
	GROUP BY JRNENTRY
	UNION ALL
	SELECT B.JRNENTRY, SUM(B.DEBITAMT - B.CRDTAMNT) AS GL_TOTAL FROM GL10000 A
	JOIN GL10001 B ON
		A.JRNENTRY = B.JRNENTRY
	WHERE TRXDATE = @DATE AND ACTINDX IN (SELECT ACTINDX FROM GL00105 WHERE ACTNUMBR_3 = '1400')
	GROUP BY B.JRNENTRY) A
	GROUP BY A.JRNENTRY) B ON
	A.JRNENTRY = B.JRNENTRY
--WHERE ((A.HB_TOTAL - B.GL_TOTAL) <> .00000
	--or b.GL_TOTAL is null)
ORDER BY A.JRNENTRY

SELECT A.*, B.*, (A.HB_TOTAL - B.GL_TOTAL) AS DIFF FROM (SELECT JRNENTRY, SUM(EXTDCOST) AS HB_TOTAL FROM SEE30303 WHERE GLPOSTDT = @DATE GROUP BY JRNENTRY) A
/*CHANGE THE JOIN FROM LEFT TO RIGHT IN ORDER TO SEE THE DIFFERENCES BOTH WAYS.*/
RIGHT JOIN (SELECT A.JRNENTRY, SUM(A.TOTAL) AS GL_TOTAL FROM (
	SELECT JRNENTRY, SUM(DEBITAMT - CRDTAMNT) AS TOTAL FROM GL20000
	WHERE TRXDATE = @DATE AND ACTINDX IN (SELECT ACTINDX FROM GL00105 WHERE ACTNUMBR_3 = '1400')
	GROUP BY JRNENTRY
	UNION ALL
	SELECT B.JRNENTRY, SUM(B.DEBITAMT - B.CRDTAMNT) AS TOTAL FROM GL10000 A
	JOIN GL10001 B ON
		A.JRNENTRY = B.JRNENTRY
	WHERE TRXDATE = @DATE AND ACTINDX IN (SELECT ACTINDX FROM GL00105 WHERE ACTNUMBR_3 = '1400')
	GROUP BY B.JRNENTRY) A
	GROUP BY A.JRNENTRY) B ON
	A.JRNENTRY = B.JRNENTRY
--WHERE ((A.HB_TOTAL - B.GL_TOTAL) <> .00000
	--or b.GL_TOTAL is null)
ORDER BY A.JRNENTRY

/*These scripts will help find out which account is causing the difference.*/
SELECT GLPOSTDT, IVIVINDX, SUM(EXTDCOST) AS TOTAL FROM SEE30303
WHERE GLPOSTDT = @DATE
GROUP BY GLPOSTDT, IVIVINDX

SELECT A.TRXDATE, A.ACTINDX, SUM(A.TOTAL) AS TOTAL FROM (
	SELECT TRXDATE, ACTINDX, SUM(DEBITAMT - CRDTAMNT) AS TOTAL FROM GL20000
	WHERE ACTINDX IN (SELECT ACTINDX FROM GL00105 WHERE ACTNUMBR_3 = '1400') GROUP BY TRXDATE, ACTINDX
	UNION ALL
	SELECT A.TRXDATE, B.ACTINDX as ACTINDX, SUM(B.DEBITAMT - B.CRDTAMNT) AS TOTAL FROM GL10000 A
	JOIN GL10001 B ON A.JRNENTRY = B.JRNENTRY
	WHERE B.ACTINDX IN (SELECT ACTINDX FROM GL00105 WHERE ACTNUMBR_3 = '1400') GROUP BY A.TRXDATE, B.ACTINDX) A
WHERE A.TRXDATE = @DATE
GROUP BY A.TRXDATE, A.ACTIND