CODEWITHSUNDEEP
sqlsql-servert-sqlaggregate-functionsaggregate
John Chrysostom
John Chrysostom

Reputation: 3963

Multiple Columns in Aggregated Expression Containing Outer Reference

I have the following portion of a larger query (outer apply is used for reasons beyond the scope of this question):

select top 100 *
from (
    select SSN, Program, Year, Cycle,
        case when (lead(Year,1,0) over (partition by SSN, Program order by Year, Cycle)=0 and Year != last_value(Year) over(order by Year ROWS between unbounded preceding and unbounded following))
            or (lead(Year,1,0) over (partition by SSN, Program order by Year, Cycle)-1900)*2+lead(Cycle,1,0) over (partition by SSN, Program order by Year, Cycle)-((Year-1900)*2+Cycle)>2
        then ((Year-1900)*2+Cycle)*2 else null end as ExitQuarter
    from dbo.Temp_Cohort
    where Program!='BSG' and Program!='BSD'
) as R
outer apply
(
    select sum(case when (Year-1900)*4+Quarter>R.ExitQuarter and (Year-1900)*4+Quarter<R.ExitQuarter+5 then Wage else null end) as Wage1,
    sum(case when (Year-1900)*4+Quarter>R.ExitQuarter+4 and (Year-1900)*4+Quarter<R.ExitQuarter+9 then Wage else null end) as Wage2,
    sum(case when (Year-1900)*4+Quarter>R.ExitQuarter+8 and (Year-1900)*4+Quarter<R.ExitQuarter+13 then Wage else null end) as Wage3,
    sum(case when (Year-1900)*4+Quarter>R.ExitQuarter+12 and (Year-1900)*4+Quarter<R.ExitQuarter+17 then Wage else null end) as Wage4,
    sum(case when (Year-1900)*4+Quarter>R.ExitQuarter+16 and (Year-1900)*4+Quarter<R.ExitQuarter+21 then Wage else null end) as Wage5
    from dbo.Wage_Hist
    where SSN=R.SSN
) as W

When I execute, I get the following error: Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

Do you have suggestions for workarounds or fixes?

Upvotes: 1

Views: 2571

Answers (2)

John Chrysostom
John Chrysostom

Reputation: 3963

Here's the final solution... Anon got me to the right place, but just needed some syntax and math fixes.

select top 100 *
from (
    select SSN, Program, Year, Cycle,
        case when (lead(Year,1,0) over (partition by SSN, Program order by Year, Cycle)=0 and Year != last_value(Year) over(order by Year ROWS between unbounded preceding and unbounded following))
            or (lead(Year,1,0) over (partition by SSN, Program order by Year, Cycle)-1900)*2+lead(Cycle,1,0) over (partition by SSN, Program order by Year, Cycle)-((Year-1900)*2+Cycle)>2
        then ((Year-1900)*2+Cycle)*2 else null end as ExitQuarter
    from dbo.Temp_Betty_Cohort
    where Program!='BSG' and Program!='BSD'
) as R
outer apply
(
    select [1] as Wage1, [2] as Wage2, [3] as Wage3, [4] as Wage4, [5] as Wage5
    from
    (
        select Wage, round(((Year-1900)*4+Quarter-R.ExitQuarter+3)/4,0,1) as Period
        from dbo.Wage_Hist
        where SSN=R.SSN
    ) as S
    pivot
    (
        sum(Wage) for Period in ([1],[2],[3],[4],[5])
    ) as P
) as H

Upvotes: 1

Anon
Anon

Reputation: 10908

select top 100 *
from (
    select SSN, Program, Year, Cycle,
        case when (lead(Year,1,0) over (partition by SSN, Program order by Year, Cycle)=0 and Year != last_value(Year) over(order by Year ROWS between unbounded preceding and unbounded following))
            or (lead(Year,1,0) over (partition by SSN, Program order by Year, Cycle)-1900)*2+lead(Cycle,1,0) over (partition by SSN, Program order by Year, Cycle)-((Year-1900)*2+Cycle)>2
        then ((Year-1900)*2+Cycle)*2 else null end as ExitQuarter
    from dbo.Temp_Cohort
    where Program!='BSG' and Program!='BSD'
) as R
outer apply
(
    SELECT [1] Wage1, [2] Wage2, [3] Wage3, [4] Wage4, [5] Wage5
    FROM (
        SELECT Wage, (R.ExitQuarter-((Year-1900)*4+Quarter)+3) / 4) WagePeriod
        FROM dbo.Wage_Hist
        WHERE SSN=R.SSN
    ) p1
    PIVOT(SUM(Wage) FOR WagePeriod IN ([1],[2],[3],[4],[5])) p2
) as W

Upvotes: 3

Related Questions