## Introduction

Suppose we have a calculated Column that is a result of some complex calculation and we want to refer it in a `where `

clause (or a `group by `

clause). This is not straight forward since we can’t use the computed Column directly in the `where `

clause like this:

Select Column1,
Column2,
Calculated_Column =
Case
When Expression1 = True Then Column1 * (Column4 - Column 5)
When Expression2 = True Then Column2 * (Column5 - Column 6)
When Expression3 = True Then Column3 * (Column6 - Column 7)
Else
Column4 - Column 5
End
From
Table
Where
Calculated_Column < 100

The reason is that the `WHERE `

part of the query is evaluated before the `Select `

part so, we need to duplicate the whole calculation in the `WHERE `

clause like this:

Where
(
Case
When Expression1 = True Then Column1 * (Column4 - Column 5)
When Expression2 = True Then Column2 * (Column5 - Column 6)
When Expression3 = True Then Column3 * (Column6 - Column 7)
Else
Column4 - Column 5
End
)< 50

Now, we need to move our calculation to a `CROSS APPLY `

section and we can refer to the calculated Column by its alias in the `WHERE `

and in the `GROUP BY `

clause as below:

Select Column1
Column2,
Calculated_Column
From
Table
Cross Apply
(
Select
Calculated_Column =
Case
When Expression1 = True Then Column1 * (Column4 - Column 5)
When Expression2 = True Then Column2 * (Column5 - Column 6)
When Expression3 = True Then Column3 * (Column6 - Column 7)
else Column4 - Column 5
End
) Calculated_Column1Query
where Calculated_Column < 50

Hurray..!! We can also use a Derived table to accomplish the same as below:

;With Calculated_Column_Calculation As
(
Select Column1,
Column2,
Calculated_Column =
Case
When Expression1 = True Then Column1 * (Column4 - Column 5)
When Expression2 = True Then Column2 * (Column5 - Column 6)
When Expression3 = True Then Column3 * (Column6 - Column 7)
Else
Column4 - Column 5
End
From
Table
)
Select *
From
Calculated_Column_Calculation
Where
Calculated_Column < 50

DONE!