===== 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_formatsql|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

(

<code>
  SELECT
</code>

<code>
    ROW_NUMBER() OVER(ORDER BY FormattedPath) AS Row,
</code>

<code>
    GLTree.FormattedName,
</code>

<code>
    coalesce((SELECT sum(Amount) FROM GL WHERE GL.GLAccountID  GLTree.NodeID AND EntryDateTime <@DateStart                   ), 0) AS PeriodStart,
</code>

<code>
    coalesce((SELECT sum(Amount) FROM GL WHERE GL.GLAccountID  GLTree.NodeID AND EntryDateTime BETWEEN @DateStart AND @DateEnd), 0) AS PeriodChange,
</code>

<code>
    coalesce((SELECT sum(Amount) FROM GL WHERE GL.GLAccountID  GLTree.NodeID AND EntryDateTime
</code>

\\