You cannot use aliases you define in the select clause within the same select clause (or anywhere else in that select statement , except in the order by clause). So what you may need to do is something like this where I am repeating the logic:CASE WHEN D1_Factor_ID = '1' THEN
SUM(EffectiveMinutes * D1_Factor_PC)/100.0
ELSE
0
END AS [Personal_Needs],
CASE WHEN D1_Factor_ID = '1' AND SUM(EffectiveMinutes * D1_Factor_PC)/100.0 <> 0 THEN
SUM(EffectiveMinutes) + SUM(EffectiveMinutes * D1_Factor_PC)/100.0
ELSE
0
END AS 'SAM Recovery + Personal Needs Total',
One other thing to keep in mind is the following: If your Effective minutes and D1_Factor_PC are integers, the resulting division will end up being an integer division, which may not be what you want. Hence my adding a ".0" (see in red) (to force it to have fractional parts) in the above query
Help with SQL IF
Posted - 05/01/2012 : 07:35:41
