Querying the General Ledger with Total Lines - Actuals

In the earlier post on this topic I included a query that would add ten columns to the basic GLAcct table. These extra columns would allow you to summarize by Total Line right in a query. In this post I will share a query that will allow you to get the period actuals for all the accounts, including total lines. It will also provide extra lines for Project values. If you want to remove the Project level detail, just remove the Project line from the SELECT statement and from the GROUP BY statement.

Most reports need to be able to report on one or more periods, so in order to generate either a pivot table with the ability to pull any period or create a Report Designer that can prompt the user for a period, we need to multiply the accounts in GLAcct thirteen times, one for each possible Period (don't forget about the Audit period, which is period 13). To do this, we will add in a Cartesian Join between GLAcct and an inline view that is nothing but the numbers 1 through 13:

select

*

from

Shelby.GLAcct, (select Period = 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12 union select 13) as Periods

We then substitute this in place of the reference to Shelby.GLAcct in the original Total Line query given in the previous post, and we have this (above query in italics below):

select

A1.CoNu,

A1.BeginDate,

A1.FundNu,

A1.DeptNu,

A1.AcctNu,

A1.LineType,

Descr = max(A1.Descr),

StmtType = max(A1.StmtType),

A1.Period,

T0 = sum(case when A2.LineType like '[0-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,

T1 = sum(case when A2.LineType like '[1-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,

T2 = sum(case when A2.LineType like '[2-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,

T3 = sum(case when A2.LineType like '[3-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,

T4 = sum(case when A2.LineType like '[4-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,

T5 = sum(case when A2.LineType like '[5-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,

T6 = sum(case when A2.LineType like '[6-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,

T7 = sum(case when A2.LineType like '[7-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,

T8 = sum(case when A2.LineType like '[8-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,

T9 = sum(case when A2.LineType like '[9-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end

from

(select * from Shelby.GLAcct, (select Period = 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12 union select 13) as Periods) as A1 left join

Shelby.GLAcct as A2 on A1.CoNu = A2.CoNu and A1.BeginDate = A2.BeginDate and (A1.FundNu > A2.FundNu or (A1.FundNu = A2.FundNu and A1.DeptNu > A2.DeptNu) or (A1.FundNu = A2.FundNu and A1.DeptNu = A2.DeptNu and A1.AcctNu >= A2.AcctNu)) and isnumeric(A2.LineType) = 1

group by

A1.CoNu,

A1.BeginDate,

A1.FundNu,

A1.DeptNu,

A1.AcctNu,

A1.LineType,

A1.Period

Now we need to take the entirety of this result and use it as an inline view, add a join to the actual transaction history table, which is GLDetail, and then construct a SELECT clause that can sum up the numbers as needed. Here is that query. (Warning: This only works on SQL Server 2005 or later. If you are running MSDE or SQL Server 2000, this will not work for you.) Also, this query will return all fiscal years of all companies. This may take a long time to run (15 mintues + for some organizations). Add a WHERE clause to limit the results to just one fiscal year and company to shorten the time. (The above query is in italics below.)

select

A.BeginDate,

A.CoNu,

A.FundNu,

A.DeptNu,

A.AcctNu,

Descr = max(A.Descr),

A.Period,

Project = (select P.Name from Shelby.SSProject as P where P.ProjectCounter = D.ProjectCounter),

PeriodActual = case A.LineType

when 'D' then isnull(sum(D.Amt), 0)

when '0' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T0), 0)

when '1' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T1), 0)

when '2' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T2), 0)

when '3' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T3), 0)

when '4' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T4), 0)

when '5' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T5), 0)

when '6' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T6), 0)

when '7' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T7), 0)

when '8' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T8), 0)

when '9' then isnull(sum(sum(D.Amt)) over (partition by A.BeginDate, A.CoNu, A.Period, A.T9), 0)

end

from

(select A1.CoNu,A1.BeginDate,A1.FundNu,A1.DeptNu,A1.AcctNu,A1.LineType,Descr = max(A1.Descr),StmtType = max(A1.StmtType), A1.Period, T0 = sum(case when A2.LineType like '[0-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T1 = sum(case when A2.LineType like '[1-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T2 = sum(case when A2.LineType like '[2-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T3 = sum(case when A2.LineType like '[3-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T4 = sum(case when A2.LineType like '[4-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T5 = sum(case when A2.LineType like '[5-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T6 = sum(case when A2.LineType like '[6-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T7 = sum(case when A2.LineType like '[7-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T8 = sum(case when A2.LineType like '[8-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T9 = sum(case when A2.LineType like '[9-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end from (select * from Shelby.GLAcct, (select Period = 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12 union select 13) as Periods) as A1 left join Shelby.GLAcct as A2 on A1.CoNu = A2.CoNu and A1.BeginDate = A2.BeginDate and (A1.FundNu > A2.FundNu or (A1.FundNu = A2.FundNu and A1.DeptNu > A2.DeptNu) or (A1.FundNu = A2.FundNu and A1.DeptNu = A2.DeptNu and A1.AcctNu >= A2.AcctNu)) and isnumeric(A2.LineType) = 1 group by A1.CoNu, A1.BeginDate, A1.FundNu, A1.DeptNu, A1.AcctNu, A1.LineType, A1.Period) as A left join

Shelby.GLDetail as D on D.BeginDate = A.BeginDate and D.CoNu = A.CoNu and D.FundNu = A.FundNu and D.DeptNu = A.DeptNu and D.AcctNu = A.AcctNu and D.Period = A.Period

group by

A.BeginDate,

A.CoNu,

A.FundNu,

A.DeptNu,

A.AcctNu,

A.LineType,

A.Period,

D.ProjectCounter,

A.T0, A.T1, A.T2, A.T3, A.T4, A.T5, A.T6, A.T7, A.T8, A.T9

order by

A.BeginDate,

A.CoNu,

A.FundNu,

A.DeptNu,

A.AcctNu

This approach uses the windowing function sum() over (partition by ...) to give a sum of the sums for the total lines. It uses the T0 through T9 columns as the leverage it needs to get the sum values. Windowing functions are great ways to do aggregate values on a scalar set of results or to aggregate an aggregate, as I did here.

This only gets the actual values. Getting the budget values is actually a bit harder, especially with a Project breakout. I'll post more query solutions with budget values in them later.

If you have questions, feel free to post a comment. I'd love to know if this blog is meeting anyones needs or at least of interest to some people.