<div>
Not sure I understand the requirments. &nbsp;Sample data ad expectedresults would help a lot. 
 
Just a guess at what you are asking: &nbsp;Remove the WHERE clause in the CTE and add it to the case. 
 

<pre><code class="code-1 nolinks" id="code-20-43074679-1">case when rn=1 and ABS(SplitRate) &gt; 15 then 1 else 0 end NewField
</code></pre>
 
Adding the extra zeros and single quotes makes it a 'string'. &nbsp;Probably not really what you want.
</div>

<div>
I agree with slightwv's solution for the newfield. &nbsp;I would also like to note that your ROWNUMBER function doesn't make much sense as the ORDER BY is not adding any granularity that isn't in the PARTITION BY. &nbsp;There is no need to reiterate the column list in the ORDER BY. &nbsp;I would expect something like... 
 
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.somedate) AS rn
</div>

<div>
@slightwv 
 
In the attached example, the final column &quot;Contractors Paid&quot; should have been 1 where the split rate was 50.. 
<a href="https://filedb.experts-exchange.com/incoming/2020/04_w18/1451532/julie_bowman_2018-Present_splitrate.xlsx" target="_blank" class="file-inline" title="julie_bowman_2018-Present_splitrate.xlsx (375 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>julie_bowman_2018-Present_splitrate.xlsx</a>
</div>

julie_bowman_2018-Present_splitrate.xlsx

<div>
This is my full query. 
 

<pre><code class="code-10 nolinks" id="code-20-43074798-1">with cte as (
	select pr.[CompanyID], pr.[JobType], pr.[TransactionType], pr.[TempID],
 DENSE_RANK() OVER(ORDER BY pr.[CustomerNumber], 
	 pr.[OfficeID], pr.[JobNumber], pr.[ConsultantID], pr.[TransactionDate] ) as [spreadID],
 pr.[CustomerNumber] as [Customer ID], 
	 cust.[CustomerName],
	 pr.[OfficeID],
	 pr.[SBUID], 
	 pr.[JobNumber], pr.[ConsultantID] as [ConsultantID Original], 
	 replace(replace(pr.[ConsultantID],'R',''),'S','') [Consultant_ID],
	 con.[FullName] as [Consultant Name], 
	 pr.[PayRate],
	 pr.[BillRate],
 pr.[SplitRate],
 pr.[PayTypeID],
	 pr.[HoursWorked], 
	 pr.[HoursBilled], 
	 CASE when pr.[PayTypeID] = 'REG' then HoursBilled else 0 end as [Regular Hours],
	 CASE when pr.[PayTypeID] = 'OT' then HoursBilled else 0 end as [OT Hours],
	 pr.[VendorCost],
	 pr.[Cost] as [Pay Amount],
	 pr.[TotalFee] as [Bill Amount], 
	 pr.[OnCostAmount] as [Burden Amount],
	 pr.[CreditedAmount],
	 NSP = pr.[TotalFee] - pr.[Cost] -(case when pr.[ExtKey1] = '0' then (
(case when pr.[TempType] = 'Employee' then [OnCostAmount] else 0 end))
else pr.[OnCostAmount] end),
 pr.[TempType] as [Contractors Paid Type],
	 pr.[TransactionDate],	
	 pr.[BatchDate], 
	 pr.[InvoiceDate], 
 pr.[InvoiceNumber], 
 pr.[Status], 
 pr.[InvoiceFrequency],
	 [BatchID],
 row_number() over(partition by RTRIM(pr.CompanyID),RTRIM(pr.CustomerNumber),RTRIM(pr.[OfficeID]),RTRIM(pr.SBUID),RTRIM(pr.JobNumber),RTRIM(pr.TempID),pr.[TransactionDate]
				order by pr.CompanyID,pr.CustomerNumber,pr.JobNumber,pr.SBUID,pr.TempID,pr.BatchID) rn
 FROM [Temp_Rept] pr
INNER JOIN [Consultants] con ON pr.ConsultantID = con.[ConsultantID]
INNER JOIN [Customers] cust ON pr.[CustomerNumber] = cust.[CustomerNumber]
WHERE [TransactionDate] &gt;= '01/01/2018'
and [FullName] LIKE '%Bowman%'
)
select [CompanyID], [JobType], [TransactionType], [TempID],
 [spreadID],
 [Customer ID], 
	 [CustomerName],
	 [OfficeID],
	 [SBUID], 
	 [JobNumber],
	 [ConsultantID Original], 
	 [Consultant_ID],
	 [Consultant Name], 
	 [PayRate],
	 [BillRate],
 [SplitRate],
 [PayTypeID],
	 [HoursWorked], 
	 [HoursBilled], 
	 [Regular Hours],
	 [OT Hours],
	 [VendorCost],
	 [Pay Amount],
	 [Bill Amount], 
	 [Burden Amount],
	 [CreditedAmount],
	 NSP,
 [Contractors Paid Type],
	 [TransactionDate],	
	 [BatchDate], 
	 [InvoiceDate], 
 [InvoiceNumber], 
 [Status], 
 [InvoiceFrequency],
	 [BatchID],
	 Case when rn=1 and ABS(SplitRate) &gt; 15 then 1 else 0 end ContractorsPaid
from cte;
</code></pre>
</div>

<div>
&gt;&gt;&quot;Contractors Paid&quot; should have been 1 where the split rate was 50.. 
 
Are you removing the ROW_NUMBER requirement? 
 
You don't have sample data where splitrate is &gt;&nbsp;50. &nbsp;You want &quot;&gt;=&quot;? 
 
The original question was &gt;&nbsp;15 now 50. 
 
Does the example I provided not give you want you want? 
 
I mean I'll mock up a test case suing that data but it will be exactly what I already suggested.
</div>

<div>
We cross posted. &nbsp;I'll look at the query. &nbsp;I'll also add CODE tags to your comment to make it more readable.
</div>

<div>
Are you saying that query doesn't work? 
 
If not what doesn't work about it?
</div>

<div>
I suppose I should also ask if that data should have been posted on a public website?
</div>

BI Director

Grayson Hampton

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

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

Create a New Field based on existing field with Where clause

T-SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.