Nested selects in joins: The multi-part identifier could not be bound

Question

Here's a nasty one. I'm trying to reference a table in an outer query from an inner select nested in a join. I need to filter these inner queries on values from the outer sales_contract table, but I receive the 'multi-part identifier could not be bound' error when I try to reference any of these columns in the inner queries:

sc.commission_range_start
sc.commission_range_end
sc.commission_duration
sc.signup_range_start
sc.signup_range_end

How can I restructure this query?

with account_hierarchy(account_major, account_minor) as
(
    select
        gp.account_major as account_major,
        gp.account_major as account_minor
    from
        group_accounts gp with(nolock)
        left join group_accounts gc with(nolock) on gp.account_major = gc.account_minor
    where
        gc.account_major is null
    group by
        gp.account_major

    union all

    select 
        up.account_major, down.account_minor
    from 
        group_accounts as down with(nolock)
        join account_hierarchy as up on up.account_minor = down.account_major
)
select
    sa.first_name as 'AgentFirstName',
    sa.last_name as 'AgentLastName',
    sc.threshold as 'CommissionUsageThreshold',
    sc.commission_amount as 'CommissionAmount',
    sc.commission_percentage as 'CommissionPercentage',
    parent.primary_phone as 'AccountNumber',
    child.primary_phone as 'ChildAccountNumber',
    coalesce(total_credit.amount, 0) as 'CreditTotal',
    coalesce(total_billing.amount, 0) as 'BillingTotal'
from
    sales_contract sc
    inner join sales_agent sa on sc.sales_agent_id = sa.id
    inner join sales_distributor sd on sa.distributor_id = sd.id
    inner join demographic parent on sc.primary_phone = parent.primary_phone
    inner join account_hierarchy ah on parent.primary_phone = ah.account_major
    inner join demographic child on ah.account_minor = child.primary_phone
    inner join
    (
        select
            d.primary_phone
        from
            demographic d
        where
            (d.active_date >= sc.signup_range_start or sc.signup_range_start is null) and
            (d.active_date < sc.signup_range_end or sc.signup_range_end is null)
    ) commission on
        child.primary_phone = commission.primary_phone
    left outer join
    (
        select
            j.primary_phone, sum(j.billed_amount) as amount
        from
            jobs_complete j
        where
            j.time_proofed >= sc.commission_range_start
            and (j.time_proofed < dateadd(month, sc.commission_duration, sc.commission_range_start))
            and (j.time_proofed < sc.commission_range_end or sc.commission_range_end is null)
        group by
            j.primary_phone
    ) total_billing on
        commission.primary_phone = total_billing.primary_phone
    left outer join
    (
        select
            c.primary_phone, sum(c.amount) as amount
        from
            credit c
        group by
            c.primary_phone
    ) total_credit on
        commission.primary_phone = total_credit.primary_phone
where
    sd.email = @DistributorEmail

Answer 1

I think that you need to change the JOINs where you are referencing the outer tables for CROSS APPLY and OUTER APPLY. Try this:

with account_hierarchy(account_major, account_minor) as
(
    select
        gp.account_major as account_major,
        gp.account_major as account_minor
    from
        group_accounts gp with(nolock)
        left join group_accounts gc with(nolock) on gp.account_major = gc.account_minor
    where
        gc.account_major is null
    group by
        gp.account_major

    union all

    select 
        up.account_major, down.account_minor
    from 
        group_accounts as down with(nolock)
        join account_hierarchy as up on up.account_minor = down.account_major
)
select
    sa.first_name as 'AgentFirstName',
    sa.last_name as 'AgentLastName',
    sc.threshold as 'CommissionUsageThreshold',
    sc.commission_amount as 'CommissionAmount',
    sc.commission_percentage as 'CommissionPercentage',
    parent.primary_phone as 'AccountNumber',
    child.primary_phone as 'ChildAccountNumber',
    coalesce(total_credit.amount, 0) as 'CreditTotal',
    coalesce(total_billing.amount, 0) as 'BillingTotal'
from
    sales_contract sc
    inner join sales_agent sa on sc.sales_agent_id = sa.id
    inner join sales_distributor sd on sa.distributor_id = sd.id
    inner join demographic parent on sc.primary_phone = parent.primary_phone
    inner join account_hierarchy ah on parent.primary_phone = ah.account_major
    inner join demographic child on ah.account_minor = child.primary_phone
    cross apply
    (
        select
            d.primary_phone
        from
            demographic d
        where
            (d.active_date >= sc.signup_range_start or sc.signup_range_start is null) and
            (d.active_date < sc.signup_range_end or sc.signup_range_end is null) and
            d.primary_phone = child.primary_phone
    ) commission 
    outer apply
    (
        select
            j.primary_phone, sum(j.billed_amount) as amount
        from
            jobs_complete j
        where
            j.time_proofed >= sc.commission_range_start
            and (j.time_proofed < dateadd(month, sc.commission_duration, sc.commission_range_start))
            and (j.time_proofed < sc.commission_range_end or sc.commission_range_end is null)
            and j.primary_phone = child.primary_phone
        group by
            j.primary_phone
    ) total_billing
    left outer join
    (
        select
            c.primary_phone, sum(c.amount) as amount
        from
            credit c
        group by
            c.primary_phone
    ) total_credit on
        commission.primary_phone = total_credit.primary_phone
where
    sd.email = @DistributorEmail

Answer 2

You could try moving the subqueries into views then joining on the views.

select ...
from
    sales_contract sc
    inner join sales_agent sa on sc.sales_agent_id = sa.id
    inner join sales_distributor sd on sa.distributor_id = sd.id
    inner join demographic parent on sc.primary_phone = parent.primary_phone
    inner join account_hierarchy ah on parent.primary_phone = ah.account_major
    inner join demographic child on ah.account_minor = child.primary_phone
    inner join vwCommission on child.primary_phone = vwCommission.primary_phone
        and ...
    left outer join vwTotal_Billing on commission.primary_phone = vwTotal_Billing.primary_phone
        and ...
    left outer join vwTotal_credit on commission.primary_phone = vwTotal_credit.primary_phone
        and ...
where ...