nested case - is there an simpler way to do this?

QtyPcntCompleted should never exceed 100 but I have no control over
what is in PostedQty or ForecastQty.
So if the calculation exceeds 100 I set it to 100.
What I'm wondering is can the nested CASE be replaced by a function?
QtyPcntCompleted = CASE WHEN ForecastQty = 0 THEN 0 ELSE CASE WHEN
(((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) > 100 THEN 100
ELSE ((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty END END
thanks in advance for your feedbackI don't think you need nested CASE statements here
SELECT 'QtyPcntCompleted'=
CASE
WHEN ForecastQty = 0 THEN CAST(0 AS decimal(5,2))
WHEN (((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) > 100 THEN
CAST(100 AS decimal(5,2))
ELSE CAST((((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) AS
decimal(5,2))
END
All the possible returned values from the CASE construct have to be of the
same data type, so I would cast the 0 and 100 values into decimal(5,2).
Otherwise, a fractional percentage may get truncated or rounded because the
CASE statement selected a datatype of integer.
"Gerard" wrote:

> QtyPcntCompleted should never exceed 100 but I have no control over
> what is in PostedQty or ForecastQty.
> So if the calculation exceeds 100 I set it to 100.
> What I'm wondering is can the nested CASE be replaced by a function?
> --
> QtyPcntCompleted = CASE WHEN ForecastQty = 0 THEN 0 ELSE CASE WHEN
> (((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) > 100 THEN 100
> ELSE ((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty END END
> --
> thanks in advance for your feedback
>|||Thanks Mark,
much appreciated
Gerard