patternMinor
MS SQL : Use Computed value to Compute other values
Viewed 0 times
computedcomputesqlothervaluevaluesuse
Problem
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
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.
I have Coupons that can be amount or percent, if percent is 0 then amount is used
CASE
WHEN c.CouponPercent > 0 THEN p.Price - (c.CouponPercent/100)
ELSE c.CouponValue
END as Couponnow I have to calculate percent for one content provider that is
(p.Price-Coupon)*(ProviderPercent/100)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.
Solution
It's simple, computed value can be used by applying is value with cross apply
like this
select cpk.coupon, p.Price, p.Price-cpk.coupon as FinalPrice -- etc
like this
select cpk.coupon, p.Price, p.Price-cpk.coupon as FinalPrice -- etc
from Product -- etc
CROSS APPLY (
select coupon = CASE
WHEN c.CouponPercent = 0 THEN p.Price - (c.CouponPercent/100)
ELSE c.CouponValue
END as Coupon ) as cpk
where --etcCode Snippets
from Product -- etc
CROSS APPLY (
select coupon = CASE
WHEN c.CouponPercent = 0 THEN p.Price - (c.CouponPercent/100)
ELSE c.CouponValue
END as Coupon ) as cpk
where --etcContext
StackExchange Database Administrators Q#2309, answer score: 4
Revisions (0)
No revisions yet.