I have the following query, which builds a new field successfully.
I need the (ABS(SplitRate) > '15.00000' in the Where clause to only be used when calculating the "NewField" results and not the entire query.
with cte as (
select pr.CompanyID, pr.JobType, pr.TempID, pr.CustomerNumber, pr.OfficeID, pr.SBUID, pr.JobNumber, pr.ConsultantID, pr.BatchID,
row_number() over(partition by pr.CompanyID, pr.JobType, pr.TempID, pr.CustomerNumber, pr.OfficeID, pr.SBUID, pr.JobNumber, pr.ConsultantID, pr.BatchID
order by pr.CompanyID, pr.JobType, pr.TempID, pr.CustomerNumber, pr.OfficeID, pr.SBUID, pr.JobNumber, pr.ConsultantID, pr.BatchID) rn
from [Temp_Rept] pr
WHERE (ABS(SplitRate) > '15.00000')
and pr.[CustomerNumber] = 113517
)
select CompanyID, JobType, TempID, CustomerNumber, OfficeID, SBUID, JobNumber, ConsultantID, BatchID,
case when rn=1 then 1 else 0 end NewField