SQL for Trial Balance in Great Plains

[mkehinde2]

Hi There,
How do I write an SQL statement to retrieve a trial balance for a month with the following columns as headers:Account Code, Account Description, Debit, Credit.
I want to show the YTD figures.

Thanks

 

[klewis10367]

Try something like this to get you started. I am sure it can be optimized but i had created it to be used in Smart List builder:

SELECT     dbo.GL00100.ACTINDX, dbo.GL00105.ACTNUMST, dbo.GL00100.ACTDESCR, dbo.GL10110.YEAR1, 
                      SUM(CASE WHEN periodid = 0 THEN perdblnc ELSE 0 END) AS BeginningBalance, SUM(CASE WHEN periodid BETWEEN 0 AND 
                      1 THEN perdblnc ELSE 0 END) AS January, SUM(CASE WHEN periodid BETWEEN 0 AND 2 THEN perdblnc ELSE 0 END) AS February, 
                      SUM(CASE WHEN periodid BETWEEN 0 AND 3 THEN perdblnc ELSE 0 END) AS March, SUM(CASE WHEN periodid BETWEEN 0 AND 
                      4 THEN perdblnc ELSE 0 END) AS April, SUM(CASE WHEN periodid BETWEEN 0 AND 5 THEN perdblnc ELSE 0 END) AS May, 
                      SUM(CASE WHEN periodid BETWEEN 0 AND 6 THEN perdblnc ELSE 0 END) AS June, SUM(CASE WHEN periodid BETWEEN 0 AND 
                      7 THEN perdblnc ELSE 0 END) AS July, SUM(CASE WHEN periodid BETWEEN 0 AND 8 THEN perdblnc ELSE 0 END) AS August, 
                      SUM(CASE WHEN periodid BETWEEN 0 AND 9 THEN perdblnc ELSE 0 END) AS September, SUM(CASE WHEN periodid BETWEEN 0 AND 
                      10 THEN perdblnc ELSE 0 END) AS October, SUM(CASE WHEN periodid BETWEEN 0 AND 11 THEN perdblnc ELSE 0 END) AS November, 
                      SUM(CASE WHEN periodid BETWEEN 0 AND 12 THEN perdblnc ELSE 0 END) AS December
FROM         dbo.GL00100 INNER JOIN
                      dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX INNER JOIN
                      dbo.GL10110 ON dbo.GL00105.ACTINDX = dbo.GL10110.ACTINDX
GROUP BY dbo.GL00100.ACTINDX, dbo.GL00105.ACTNUMST, dbo.GL10110.YEAR1, dbo.GL00100.ACTDESCR

This sums the Deit and Credit.