Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm having problem with generating results which are computed from data that exists in database. This is the case

I have Coupons that can be amount or percent, if percent is 0 then amount is used

  WHEN c.CouponPercent > 0 THEN p.Price - (c.CouponPercent/100)
  ELSE c.CouponValue
END as Coupon

now I have to calculate percent for one content provider that is


How can I use calculate coupon value from above to calculate Provider percent, if I use it as in code above error is reported "Invalid column name 'Coupon'".

Please help me, i Know how to resolve this on difficult way which is calculate Coupon value each time when calculating ProviderPercent but I have 8 percents like this in one report for different providers.

So basically question is how to use computed Coupon value to use it in another calculations for same record. Coupon is changed regarding product, so i can be different for every record selected from database.

share|improve this question
Your description doesn't seem to match your code. Did you mean CASE WHEN c.CouponPercent = 0 Then c.CouponValue ELSE p.Price * (c.CouponPercent/100) END as Coupon? – Leigh Riffel Apr 21 '11 at 14:31
yes I meant that, thanks – Senad Meškin Apr 21 '11 at 14:39
up vote 3 down vote accepted

Can you not do this with a CROSS APPLY?

share|improve this answer

It's simple, computed value can be used by applying is value with cross apply

like this select, p.Price, as FinalPrice -- etc

from Product -- etc
   select coupon = CASE 
  WHEN c.CouponPercent = 0 THEN p.Price - (c.CouponPercent/100)
  ELSE c.CouponValue
END as Coupon ) as cpk

where --etc
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.