This SQL function can be used to return a tree'd representation of the GL Accounts. It should be used as the basis for any financial report that uses the GL Account structure.

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

--declare @SpacesPerLevel int;  -- Set this to a value to insert that many spaces per child level
--set @SpacesPerLevel = 4;
CREATE FUNCTION GLAccountTree (@SpacesPerLevel AS INT)
RETURNS @GLTree TABLE (
  ID INT,
  AccountName VARCHAR(100),
  Hierarchy VARCHAR(500),
  ParentID INT,
  Level INT,
  GLClassificationType INT,
  SortIndex INT,
  IsActive bit,
  IsGroup bit,
  Level1Name VARCHAR(100),
  Level2Name VARCHAR(100),
  Level3Name VARCHAR(100),
  Level4Name VARCHAR(100),
  Level5Name VARCHAR(100),
  Level1ID INT,
  Level2ID INT,
  Level3ID INT,
  Level4ID INT,
  Level5ID INT,
  SortHierarchy VARCHAR(100)
)
AS
BEGIN
	DECLARE @ShowGroups   bit;  -- Set this to 1 to include the groups 
	DECLARE @ShowAccounts bit;  -- Set this to 1 to include the accounts
	SET @ShowGroups     = 1;
	SET @ShowAccounts   = 1;
	-- Build Level 1
	INSERT INTO @GLTree
	  SELECT GLAccount.ID, GLAccount.AccountName, GLAccount.AccountName, NULL, 1, 
			 GLAccount.GLClassificationType, GLAccount.SortIndex, GLAccount.IsActive, 1, 
			 NULL, NULL, NULL, NULL, NULL,
			 NULL, NULL, NULL, NULL, NULL,
 
			 RIGHT('000000000'+CAST(round(COALESCE(GLAccount.SortIndex,0)*1000, 0) AS VARCHAR(9)), 9)
	  FROM GLAccount 
	  WHERE 
		GLAccount.ID > 0 
		AND GLAccount.ClassTypeID = 8000 
		AND ( GLAccount.AccountGroupID IS NULL OR GLAccount.AccountGroupID NOT IN (SELECT ID FROM GLAccount) )
	-- Build Levels 2 through 5
	DECLARE @Depth INT;
	SET @Depth = 2;
	while (@Depth  0) 
	BEGIN
		UPDATE @GLTree
		SET AccountName = SPACE((Level-1)*@SpacesPerLevel) + AccountName;
	END
	RETURN
END

This code will pull all the accounts and compute their GL Sum for a time period.

SELECT ID, AccountName, Hierarchy, 
       ( SELECT SUM(Amount) 
         FROM GL 
         WHERE GL.GLAccountID = GLTree.ID
           AND GL.EntryDateTime < '1/1/2010'
       ) AS EOY2009Balance,
       SortHierarchy 
FROM GLAccountTree(4) GLTree
ORDER BY SortHierarchy

• Entered : //2009
• Version :