with p as (
select left( assignedContractNbr, 7 ) as blacksmithProgram
from tbl_TPM_Contracts where charindex( '_', assignedContractNbr ) = 8 and ownerId = 1
),
x as (
select blacksmithProgram, count(*) contractCount
from p
group by blacksmithProgram
having count(*) > 1
)
select c.contractId, c.assignedContractNbr, count( distinct r.requestId ) requestCount
from tbl_TPM_Contracts c
inner join tbl_TPM_Requests r
on r.ownerId = c.ownerId
and r.ownerType = c.ownerType
and r.contractId = c.contractId
where c.ownerId = 1
and c.ownerType ='MFR'
and charindex( '_', c.assignedContractNbr ) = 8
and not exists ( select * from x where x.blacksmithProgram = left( c.assignedContractNbr, 7 ))
and not exists ( select * from tbl_TPM_Contracts t where t.ownerId = 1 and t.ownerType = 'MFR' and t.assignedContractNbr = left( c.assignedContractNbr, 7 ))
group by c.contractId, c.assignedContractNbr
update tbl_TPM_Contracts
set assignedContractNbr = ,
obsoleteAssignedContractNbr =
where ownerId = 1
and ownerType ='MFR'
and assignedContractNbr =
and contractId =
update tbl_TPM_Requests
set assignedContractNbr =
where ownerId = 1
and ownerType ='MFR'
and assignedContractNbr =
and contractId =