Explanation of SQL
This SQL provides a “dump” of all of the GL data for each month in the report period, organized by the GL accounts in a tree fashion.
This is a good source of data for Cross-Tab reports (Pivot-Table in Excel) for budgeting.
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
DECLARE @DateStart datetime; DECLARE @DateEnd datetime; DECLARE @ShowZero bit; DECLARE @ShowInactive bit; -- to use in a SQL report, uncomment these lines -- to use in SQL Server, comment these lines --SET @DateEnd = ; --SET @DateStart = ; --SET @ShowZero = ; --SET @ShowInActive = ; -- to use in SQL Server, uncomment these lines -- to use in a SQL report, comment these lines SET @DateStart = '1/1/2010'; SET @DateEnd = '12/31/2010 23:59'; SET @ShowZero = 0; SET @ShowInactive = 0; DECLARE @Spacing INT; SET @Spacing = 4; SELECT * FROM ( SELECT Periods.TheYear, Periods.TheMonth, FormattedName, (SELECT COALESCE(SUM(Amount),0) FROM GL WHERE EntryDateTime BETWEEN @DateStart AND @DateEnd -- unnecessary, but easier for SQL to optimize AND GLAccountID = NodeID AND YEAR(EntryDateTime) = Periods.TheYear AND MONTH(EntryDateTime) = Periods.TheMonth) AS Amount, IsActive, NodeName, FormattedPath, ExportName, ExportNumber, CASE WHEN NodeClassTypeID = 8000 THEN 1 ELSE 0 END AS IsCategory, NodeID, NodeClassTypeID, GLClassificationType FROM ( SELECT * FROM ( SELECT CASE WHEN IsLevel1=1 THEN SPACE(@Spacing * 0) ELSE CASE WHEN IsLevel2=1 THEN SPACE(@Spacing * 1) ELSE CASE WHEN IsLevel3=1 THEN SPACE(@Spacing * 2) ELSE CASE WHEN IsLevel4=1 THEN SPACE(@Spacing * 3) ELSE SPACE(@Spacing * 4) END END END END + COALESCE(Level5.AccountName, Level4.AccountName, Level3.AccountName, Level2.AccountName, Level1.AccountName) AS FormattedName, COALESCE(Level5.AccountName, Level4.AccountName, Level3.AccountName, Level2.AccountName, Level1.AccountName) AS NodeName, COALESCE(Level5.ExportAccountName, Level4.ExportAccountName, Level3.ExportAccountName, Level2.ExportAccountName, Level1.ExportAccountName, '') AS ExportName, COALESCE(Level5.ExportAccountNumber, Level4.ExportAccountNumber, Level3.ExportAccountNumber, Level2.ExportAccountNumber, Level1.ExportAccountNumber, 0) AS ExportNumber, COALESCE(Level5.ID, Level4.ID, Level3.ID, Level2.ID, Level1.ID) AS NodeID, COALESCE(Level5.ClassTypeID, Level4.ClassTypeID, Level3.ClassTypeID, Level2.ClassTypeID, Level1.ClassTypeID) AS NodeClassTypeID, COALESCE(Level5.IsActive, Level4.IsActive, Level3.IsActive, Level2.IsActive, Level1.IsActive) AS IsActive, COALESCE(Level5.AccountGroupID, Level4.AccountGroupID, Level3.AccountGroupID, Level2.AccountGroupID, Level1.AccountGroupID) AS ParentID, (IsLevel4 * 4 + IsLevel3 * 3 + IsLevel2 * 2 + IsLevel1 * 1) + COALESCE(Level5.ID - Level5.ID + 5, 0) AS Depth, COALESCE(Level5.GLClassificationType, Level4.GLClassificationType, Level3.GLClassificationType, Level2.GLClassificationType, Level1.GLClassificationType) AS GLClassificationType, Level1.AccountName AS Level1Name, Level2.AccountName AS Level2Name, Level3.AccountName AS Level3Name, Level4.AccountName AS Level4Name, Level5.AccountName AS Level5Name, Level1.ID AS Level1ID, Level1.AccountGroupID AS Level2ID, Level2.AccountGroupID AS Level3ID, Level3.AccountGroupID AS Level4ID, Level4.AccountGroupID AS Level5ID, IsLevel1, IsLevel2, IsLevel3, IsLevel4, CASE WHEN IsLevel1 + IsLevel2 + IsLevel3 + IsLevel4 = 0 THEN 1 ELSE 0 END AS IsLevel5, CASE WHEN Level1.ID = 1 THEN '1 ' -- assets WHEN Level1.ID = 2 THEN '2 ' -- liabilities WHEN Level1.ID = 3 THEN '3 ' -- equity WHEN Level1.ID = 4 THEN '4 ' -- income WHEN Level1.ID = 7 OR Level1.AccountName IN ('COGS', 'Cost of Goods', 'Cost of Goods Sold') THEN '5 ' -- cogs WHEN Level1.ID = 5 THEN '6 ' -- expenses ELSE '9 ' -- unknown END + COALESCE(Level1.AccountName + N'\', '') + coalesce(Level2.AccountName + N'\', '') + coalesce(Level3.AccountName + N'\', '') + coalesce(Level4.AccountName + N'\', '') + coalesce(Level5.AccountName + N'\', '') as FormattedPath from GLAccount as Level1 JOIN (SELECT 1 AS IsLevel1 UNION SELECT 0) Temp1 ON 1=1 -- create two rows for each of the above left join GLAccount as Level2 on Level1.ID = Level2.AccountGroupID and IsLevel1 = 0 JOIN (SELECT 1 AS IsLevel2 UNION SELECT 0) Temp2 ON 1=1 -- create two rows for each of the above left join GLAccount as Level3 on Level2.ID = Level3.AccountGroupID and IsLevel1 = 0 and IsLevel2 = 0 JOIN (SELECT 1 AS IsLevel3 UNION SELECT 0) Temp3 ON 1=1 -- create two rows for each of the above left join GLAccount as Level4 on Level3.ID = Level4.AccountGroupID and IsLevel1 = 0 and IsLevel2 = 0 and IsLevel3 = 0 JOIN (SELECT 1 AS IsLevel4 UNION SELECT 0) Temp4 ON 1=1 -- create two rows for each of the above left join GLAccount as Level5 on Level4.ID = Level5.AccountGroupID and IsLevel1 = 0 and IsLevel2 = 0 and IsLevel3 = 0 and IsLevel4 = 0 where ( (IsLevel1=1 and Level2.ID is NULL ) or (IsLevel2=1 and Level2.ID is not null and Level3.ID is NULL) or (IsLevel3=1 and Level3.ID is not null and Level4.ID is NULL) or (IsLevel4=1 and Level4.ID is not null and Level5.ID is NULL) or ( Level5.ID is not NULL) ) and (IsLevel1 + IsLevel2 + IsLevel3 + IsLevel4 = 4000)) ORDER By TheYear, TheMonth, FormattedPath
Version Information
- Entered : 1/2012
- Version : Control 4.5+
You could leave a comment if you were logged in.