Explanation of SQL

This SQL (Report) can be used to pull the Trial Balance for any period in an easy-to-export format.

Sample Export

RowID FormattedName StartingBalance ChangeAmount EndingBalance
1 Assets NULL NULL NULL
2 > Undeposited Funds NULL NULL NULL
1 » Undeposited Cash & Checks 50.00 123.50 173.50
1 » Undeposited AmEx 800.00 0.00 800.00
1 » Undeposited Visa_MC 1024.00 512.00 1536.00
1 > Inventory Assets NULL NULL NULL

Risk of Data Corruption if Run Improperly

None. This is a selection query and no data is modified in the running of it.

SQL

code format"sql"

DECLARE @DateStart datetime;

DECLARE @DateEnd datetime;

DECLARE @ShowUnUsed bit;

DECLARE @ShowInactive bit;

SET @DateStart ;

SET @DateEnd ;

SET @ShowUnUsed ;

SET @ShowInActive ;

–SET @DateStart '1/1/2009';

–SET @DateEnd '1/1/2010';

–SET @ShowUnUsed 1;

–SET @ShowInactive 0;

DECLARE @Spacing int;

SET @Spacing 4;

SELECT Row AS RowID, FormattedName, PeriodStart AS StartingBalance, PeriodChange AS ChangeAmount, PeriodEnd AS EndingBalance

FROM

(

  SELECT
    ROW_NUMBER() OVER(ORDER BY FormattedPath) AS Row,
    GLTree.FormattedName, 
    coalesce((SELECT sum(Amount) FROM GL WHERE GL.GLAccountID  GLTree.NodeID AND EntryDateTime < @DateStart                   ), 0) AS PeriodStart,
    coalesce((SELECT sum(Amount) FROM GL WHERE GL.GLAccountID  GLTree.NodeID AND EntryDateTime BETWEEN @DateStart AND @DateEnd), 0) AS PeriodChange,
    coalesce((SELECT sum(Amount) FROM GL WHERE GL.GLAccountID  GLTree.NodeID AND EntryDateTime 
You could leave a comment if you were logged in.