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;
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) )
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