Q:

Can someone tell me whats wrong in my query? It says Msg 207, Level 16, State 1, Line 1 Invalid column name 'advance'

Select Bills.BillingPeriod, Bills.Description, Amount=iif(payment.AccountNumber is null,Bills.Amount,Bills.Amount-payment.Recieved-payment.advance) From Bills  
Left Outer Join (select [AccountNumber],[BillingPeriod],Recieved=sum([Recieved])
from Payment group by [AccountNumber],[BillingPeriod] ) 

On Payment.AccountNumber = Bills.AccountNumber And Payment.BillingPeriod = Bills.BillingPeriod Where (Payment.AccountNumber is null or Bills.Amount>Payment.Recieved) And Bills.AccountNumber ='002-002' order bybills.BillingPeriod 

A:

There is no advance column in your query, try something like this:
select 
   Bills.BillingPeriod
 , Bills.Description
 , Amount = iif(pmt.AccountNumber is null
                , Bills.Amount
                , Bills.Amount - pmt.Recieved - pmt.advance)
from Bills
  left join (
     select 
        AccountNumber
      , BillingPeriod
      , Recieved = sum(Recieved)
      , Advance = sum(Advance) /* you didn't have this column */
     from Payment
     group by 
        AccountNumber
      , BillingPeriod
    ) pmt /* you should alias the subquery */
    on pmt.AccountNumber = Bills.AccountNumber 
    and pmt.BillingPeriod = Bills.BillingPeriod
  where (pmt.AccountNumber is null or Bills.Amount > pmt.Recieved) 
    and Bills.AccountNumber = '002-002'
  order by bills.BillingPeriod

