Simple BFS Query for YTD/Annual Values and No Projects

I was recently asked to help a customer with a Budgeted Financial Statement type query that could be pushed to the Shelby v.5 Dashboard. The customer was only looking for year-to-date actual values and annual budget values. There was no need to break out by Project code. Such a request greatly simplified the query writing process over what I have been doing and documenting in previous posts. Without needing to break out Project codes I could use the GLSummary table, which already aggregates the actual transactions for each period for each account. Without needing to do any specific period reporting, I could use the GLBudget table in its native form, without unpivoting it into periodic rows. For this project I also ignored budget revisions, which further simplifies the query.

I have also discovered recently that ShelbyQUERY supports the use of WITH to define inline views before the main query's SELECT clause, which is great because if you need the same view multiple times in the main query, you can use an alias instead of typing the whole thing each time. It also moves the bulk of the view syntax out of the FROM clause, meaning that the main query's FROM clause is much more readable. One thing to remember if you are using WITH to define an inline view is that any lines of SQL code that come before it must be terminated with a semicolon. The only thing that might come in front of that in ShelbyQUERY would be variable DECLARE and SET commands. Generally the semicolon is optional for DECLARE and SET, but if there is a WITH, the semicolon is not optional.

In the query below I used a WITH statement to define the subquery that adds the columns that make it possible to do Total Line aggregates to the basic GLAcct table. I have already explained how that piece works in previous posts. I also have five variables so that the user can define a fiscal year, a company number, a fund number range, a department number range, and whether or not the Period 13 (commonly called the audit period) will be included in the calculations.

declare @FiscalYear as smallint, @CoNu as tinyint, @FundStart as smallint, @FundEnd as smallint, @DeptStart as smallint, @DeptEnd as smallint, @UsePeriod13 as varchar(1);

set @FiscalYear = 2008;

set @CoNu = 1;

set @FundStart = 0;

set @FundEnd = 999;

set @DeptStart = 0;

set @DeptEnd = 999;

set @UsePeriod13 = 'Y'; -- Change to 'N' to exclude Period 13

with GLAccounts as (select A1.CoNu, A1.BeginDate, A1.FundNu, A1.DeptNu, A1.AcctNu, LineType = A1.LineType, Descr = max(A1.Descr), StmtType = max(A1.StmtType),

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

Shelby.GLAcct 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

where year(A1.BeginDate) = @FiscalYear and A1.CoNu = @CoNu and A1.FundNu between @FundStart and @FundEnd and A1.DeptNu between @DeptStart and @DeptEnd and A1.StmtType in ('I', 'E')

group by A1.CoNu, A1.BeginDate, A1.FundNu, A1.DeptNu, A1.AcctNu, A1.LineType)

select

FiscalYear = @FiscalYear,

FundNu = GLAccounts.FundNu,

DeptNu = GLAccounts.DeptNu,

AcctNu = GLAccounts.AcctNu,

AcctDescription = GLAccounts.Descr,

YTD_Actual = case GLAccounts.StmtType when 'I' then -1 else 1 end * case GLAccounts.LineType when 'D' then GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 When 'Y' then GLSummary.Amt13 else 0 end

when '0' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T0)

when '1' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T1)

when '2' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T2)

when '3' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T3)

when '4' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T4)

when '5' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T5)

when '6' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T6)

when '7' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T7)

when '8' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T8)

when '9' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T9)

end,

Annual_Budget = case GLAccounts.StmtType when 'I' then -1 else 1 end * case GLAccounts.LineType when 'D' then GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end

when '0' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T0)

when '1' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T1)

when '2' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T2)

when '3' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T3)

when '4' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T4)

when '5' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T5)

when '6' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T6)

when '7' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T7)

when '8' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T8)

when '9' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T9)

end,

Annual_Budget_Remaining = (case GLAccounts.StmtType when 'I' then -1 else 1 end * case GLAccounts.LineType when 'D' then GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end

when '0' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T0)

when '1' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T1)

when '2' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T2)

when '3' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T3)

when '4' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T4)

when '5' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T5)

when '6' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T6)

when '7' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T7)

when '8' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T8)

when '9' then sum(GLBudget.Bud01 + GLBudget.Bud02 + GLBudget.Bud03 + GLBudget.Bud04 + GLBudget.Bud05 + GLBudget.Bud06 + GLBudget.Bud07 + GLBudget.Bud08 + GLBudget.Bud09 + GLBudget.Bud10 + GLBudget.Bud11 + GLBudget.Bud12 + case @UsePeriod13 when 'Y' then GLBudget.Bud13 else 0 end) over (partition by T9)

end) - (case GLAccounts.StmtType when 'I' then -1 else 1 end * case GLAccounts.LineType when 'D' then GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 When 'Y' then GLSummary.Amt13 else 0 end

when '0' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T0)

when '1' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T1)

when '2' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T2)

when '3' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T3)

when '4' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T4)

when '5' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T5)

when '6' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T6)

when '7' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T7)

when '8' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T8)

when '9' then sum(GLSummary.Amt01 + GLSummary.Amt02 + GLSummary.Amt03 + GLSummary.Amt04 + GLSummary.Amt05 + GLSummary.Amt06 + GLSummary.Amt07 + GLSummary.Amt08 + GLSummary.Amt09 + GLSummary.Amt10 + GLSummary.Amt11 + GLSummary.Amt12 + case @UsePeriod13 when 'Y' then GLSummary.Amt13 else 0 end) over (partition by T9)

end)

from

GLAccounts left join

Shelby.GLSummary as GLSummary on GLAccounts.CoNu = GLSummary.CoNu and GLAccounts.BeginDate = GLSummary.BeginDate and GLAccounts.FundNu = GLSummary.FundNu and GLAccounts.DeptNu = GLSummary.DeptNu and GLAccounts.AcctNu = GLSummary.AcctNu left join

Shelby.GLBudget as GLBudget on GLAccounts.CoNu = GLBudget.CoNu and GLAccounts.BeginDate = GLBudget.BeginDate and GLAccounts.FundNu = GLBudget.FundNu and GLAccounts.DeptNu = GLBudget.DeptNu and GLAccounts.AcctNu = GLBudget.AcctNu

order by FundNu, DeptNu, AcctNu