Avatar of Grayson Hampton
Grayson Hampton

asked on 

Create a New Field based on existing field with Where clause

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
* T-SQLSQL

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon