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 endfrom (`*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) = 1group 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) endfrom (`*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.Periodgroup 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.T9order 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.

## No comments:

## Post a Comment