Open Forum

  • 1.  Trying to match GL to Trial Balance

    GPUG ALL STAR
    Posted Oct 29, 2021 01:59 PM
    We're trying to create a report that historically matches the GL entries against the Trial Balance (not the historical aged trial balance).  We're trying to get the entries by month for each account and we started with Victoria Yudin's GL views, but we can't get the resulting report to match to the Trial Balance.  Our financial person claims that the Trial Balance always matches to the financial statements.  Can someone point me in a direction to try to resolve the differences?

    Here's the SQL:
    select
    AccountKey,
    DivisionKey,
    TrxMonth,
    SUM(Total) as TrxMonthTotal
    from
    (	select
    	GM.ACTNUMST As AccountKey,
    	GA.ACTNUMBR_1 As DivisionKey,
    	case 
    		when MONTH(GL.TRXDATE) = 8 then CONCAT(GL.YEAR1, 'M1')
    		when MONTH(GL.TRXDATE) = 9 then CONCAT(GL.YEAR1, 'M2')
    		when MONTH(GL.TRXDATE) = 11 then CONCAT(GL.YEAR1, 'M4')
    		when MONTH(GL.TRXDATE) = 12 then CONCAT(GL.YEAR1, 'M5')
    		when MONTH(GL.TRXDATE) = 10 then CONCAT(GL.YEAR1, 'M3')
    		when MONTH(GL.TRXDATE) = 2 then CONCAT(GL.YEAR1, 'M7')
    		when MONTH(GL.TRXDATE) = 3 then CONCAT(GL.YEAR1, 'M8')
    		when MONTH(GL.TRXDATE) = 4 then CONCAT(GL.YEAR1, 'M9')
    		when MONTH(GL.TRXDATE) = 1 then CONCAT(GL.YEAR1, 'M6')
    		when MONTH(GL.TRXDATE) = 5 then CONCAT(GL.YEAR1, 'M10')
    		when MONTH(GL.TRXDATE) = 6 then CONCAT(GL.YEAR1, 'M11')
    		when MONTH(GL.TRXDATE) = 7 then CONCAT(GL.YEAR1, 'M12')
    	end as TrxMonth,
    	GL.DEBITAMT-GL.CRDTAMNT as Total
    
    	from
    	(select ACTINDX, OPENYEAR YEAR1, TRXDATE, JRNENTRY, ORTRXSRC,
    			REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT,
    			CRDTAMNT, CURNCYID, USWHPSTD
    	 from GL20000
    	 where SOURCDOC not in ('BBF','P/L') 
    	 and VOIDED = 0
    	 and OPENYEAR > YEAR(GETDATE()) - 5
     
    	 union all
     
    	 select ACTINDX, HSTYEAR YEAR1, TRXDATE, JRNENTRY, ORTRXSRC,
    			REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT,
    			CRDTAMNT, CURNCYID, USWHPSTD
    	 from GL30000
    	 where SOURCDOC not in ('BBF','P/L') 
    	 and VOIDED = 0
    	 and HSTYEAR > YEAR(GETDATE()) - 5
    
    	 ) GL
     
    	inner join GL00105 GM
    		 on GL.ACTINDX = GM.ACTINDX
    	inner join GL00100 GA
    		 on GL.ACTINDX = GA.ACTINDX
    	inner join GL00102 C
    		 on GA.ACCATNUM= C.ACCATNUM
    	) T
    group by 
    	AccountKey,
    	DivisionKey,
    	TrxMonth​


    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise Idaho GPUG Chapter Leader
    GPUG All-Star
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Trying to match GL to Trial Balance

    Posted 29 days ago
    My guess in looking at your script is that the issue is due to excluding beginning balances for the balance sheet accounts which you need to get a true balance. If you look at the wonderful Victoria's views, you'll see that she includes them.

    ------------------------------
    Sally Higgins
    efs consulting, inc
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role