Using Calculated column in Where to filter

Transact-SQL
1

Hello,
am looking for the best, or most efficient way to implement a store procedure where I have several calculated fields and would like to use those fields in the where clause to filter on.
Any help will be greatly appreciated. Thank You..
example part of the SP:

@ID AS INT,
@Year AS SMALLINT ,
@FYTD_Min DECIMAL(18,2) = NULL,
@CYTD_Min DECIMAL(18,2) = NULL,
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN
SELECT
Pt.ID
At.Name,
At.address,
Pt.PayAmount,
Pt.month,
Pt.Year,
CASE when (Pt.Year = @Year AND Pt.month IN (1,2,3,4,5,6,7,8,9,10,11,12)) THEN
SUM(PayAmount)
ELSE 0
END AS FYTD,

CASE
WHEN (Pt.Year = @Year - 1 AND Pt.month IN (7,8,9,10,11,12) OR Pt.Year = @Year AND Pt.month IN (1,2,3,4,5,6) ) THEN
SUM(PayAmount)
ELSE 0
END AS CYTD

FROM Payment_Table AS Pt
LEFT OUTER JOIN Addres_Table AS At

WHERE Pt.ID = @ID
AND Pt.Year = @year
AND ((FYTD = @FYTD_Min) OR @FYTD_Min IS NULL)) ********
AND ((CYTD = @CYTD_Min) OR @CYTD_Min IS NULL)) ********

GROUP By Pt.ID
At.Name,
At.address
Pt.PayAmount,
Pt.month,
Pt.Year

***** IF the user leave the FYTD and CYTD field blank then all records are returned. If the user enter an amount then its filter base on the amount entered.
I am not sure if you have to use CTE, Temp table or another clever method. Also how is the best way to initialize the parameters (@FYTD_Min, @CYTD_Min) for
this situation.. Thank you

2

create procedure sp_Payments
(
@ID AS INT,
@Year AS SMALLINT ,
@FYTD_Min DECIMAL(18,2) = NULL,
@CYTD_Min DECIMAL(18,2) = NULL
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
create table #temp
(
ID INT,
Name varchar(50),
Address varchar(max),
PayAmount bigint,
Month tinyint,
Year int,
FYTD int,
CYTD int
)

insert into #temp
SELECT
Pt.ID
At.Name,
At.address,
Pt.PayAmount,
Pt.month,
Pt.Year,
CASE when (Pt.Year = @Year AND Pt.month IN (1,2,3,4,5,6,7,8,9,10,11,12)) THEN
SUM(PayAmount)
ELSE 0
END AS FYTD,

		CASE
		WHEN (Pt.Year = @Year - 1 AND Pt.month IN (7,8,9,10,11,12) OR Pt.Year = @Year AND Pt.month IN (1,2,3,4,5,6) ) THEN
		SUM(PayAmount)
		ELSE 0
		END AS CYTD

		FROM Payment_Table AS Pt
		LEFT OUTER JOIN Addres_Table AS At
		WHERE Pt.ID = @ID AND Pt.Year = @year
		GROUP By Pt.ID
		At.Name,
		At.address,
		Pt.PayAmount,
		Pt.month,
		Pt.Year

select * from #temp
where
((FYTD = @FYTD_Min) OR @FYTD_Min IS NULL)
((CYTD = @CYTD_Min) OR @CYTD_Min IS NULL)
end

3

The main clauses of a SQL Statement are evaluated in the following order:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

If you put your CASE statements into CROSS APPLY(VALUES(... they are effectively evaluated with the FROM clause and the results are available in the WHERE clause. (If you leave the CASE statements in the SELECT then only ORDER BY can use the result.)

Why do you have CASE WHEN (Pt.Year = @Year - 1...?
It will never be true as you have Pt.Year = @Year in the WHERE clause.