Create new field based on results of existing field

>>try customize the part that highlighted above when it's necessary.

You may need to tweak the "order by" as well.  IT all depends on the requirements.  In this case, for duplicates across ALL columns, you need ALL columns in the partition by.  May not in the order by but for grins, I added them.

Thank you slightwv and Ryan Chong.

Results:
CompanyID      JobType      TempID      CustomerNumber      OfficeID      SBUID      JobNumber      ConsultantID      BatchID      NewField
ADSE       Temp Job      2449129              113517               DENVER               ADMN       131705                 2375138R             TSB1.6.18A           1
ADSE       Temp Job      2449129              113517               DENVER               ADMN       131705                 2375138S             TSB1.6.18A           1
ADSE       Temp Job      2642569              113517               DENVER               ADMN       132512                 2375138S             TSB1.6.18B           1


Expected results(the second BatchID "TSB1.6.18A" should be 0):
CompanyID      JobType      TempID      CustomerNumber      OfficeID      SBUID      JobNumber      ConsultantID      BatchID      NewField
ADSE       Temp Job      2449129              113517               DENVER               ADMN       131705                 2375138R             TSB1.6.18A           1
ADSE       Temp Job      2449129              113517               DENVER               ADMN       131705                 2375138S             TSB1.6.18A           0
ADSE       Temp Job      2642569              113517               DENVER               ADMN       132512                 2375138S             TSB1.6.18B           1


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 Rept] pr
      where [TransactionDate] = '01/06/2018'
      and pr.ConsultantID like '2375138%'
      and pr.[CustomerNumber] = 113517
      and OfficeId = 'DENVER'
)
select CompanyID, JobType, TempID, CustomerNumber, OfficeID, SBUID, JobNumber, ConsultantID, BatchID,
      case when rn=1 then 1 else 0 end NewField
from cte;

The consultantID is different which is why you get a 1.  2375138R and 2375138S for the same job number.  So, they aren't "duplicates".



Figure out the fields that determine "distinct" rows and make sure those are the ones in the "partition by" clause.

Depending on which row you want to be 1 and which ones are 0, those are the columns you need to care about in the "order by".

Thanks Ryan Chong and slightwv. I have a part 2 to this question... I will open a new question to ask it.

Thank you both for being very helpful.