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.

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

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
  • Entered : 1/2012
  • Version : Control 4.5+
You could leave a comment if you were logged in.