select distinct c.ownerId, c.ownerType, c.claimId, cc.reconciled
case when tas.claimId is not null then 'Y' else 'N' end existingClaim
from tbl_TPM_Claims c with (nolock)
left outer join tbl_TPM_ClaimContracts cc with (nolock)
on cc.ownerId = c.ownerId
and cc.ownerType = c.ownerType
and cc.claimId = c.claimId
left outer join tbl_TPM_ActivityStats tas
on tas.claimId = c.claimId
where c.ownerType = 'MFR'
and c.ownerId > 0
select distinct c.claimId
from tbl_TPM_Claims c with (nolock)
left outer join tbl_TPM_ClaimContracts cc with (nolock)
on cc.ownerId = c.ownerId
and cc.ownerType = c.ownerType
and cc.claimId = c.claimId
inner join tbl_TPM_ActivityStats tas with (nolock)
on tas.ownerId = c.ownerId
and tas.ownerType = c.ownerType
and tas.claimId = c.claimId
and tas.claimType = c.claimType
and tas.claimDate = c.claimDate
and coalesce( tas.preparerUserId, 0 ) = coalesce( c.processorUserId, 0 )
and tas.checkRequested = c.checkRequested
and coalesce( tas.checkRequestedDate, 0 ) = coalesce( c.checkRequestedDate, 0 )
and coalesce( tas.checkRequestedBy, 0 ) = coalesce( c.checkRequestedBy, 0 )
and coalesce( tas.claimInvoice, '' ) = coalesce( c.claimInvoice, '' )
and coalesce( tas.claimRef, '' ) = coalesce( c.claimRef, '' )
and tas.readyToAudit = c.readyToAudit
and tas.readyToClear = c.readyToClear
and coalesce( tas.auditorUserId, 0 ) = coalesce( cc.auditorUserId, 0 )
and tas.reconciled = cc.reconciled
and coalesce( tas.reconciledDate, 0 ) = coalesce( cc.reconciledDate, 0 )
and coalesce( tas.reconciledByUserId, 0 ) = coalesce( cc.reconciledByUserId, 0 )
where c.ownerId =
and c.ownerType =
and c.claimId =
select c.ownerId, c.ownerType, c.claimId , c.claimType, c.claimDate, c.createDate, c.createUserId, c.partnerId, c.partnerType,
c.processorUserId, c.checkRequested, c.checkRequestedDate, c.checkRequestedBy, c.claimInvoice, c.claimRef, c.readyToAudit, c.readyToClear,
cc.auditorUserId, cc.reconciled, cc.reconciledDate, cc.reconciledByUserId, count( cc.contractNbr ) as subClaimCount
from tbl_TPM_Claims c with (nolock)
left outer join tbl_TPM_ClaimContracts cc with (nolock)
on cc.claimId = c.claimId
where c.ownerId =
and c.ownerType =
and c.claimId =
group by c.ownerId, c.ownerType, c.claimId , c.claimType, c.claimDate, c.createDate, c.createUserId, c.partnerId, c.partnerType,
c.processorUserId, c.checkRequested, c.checkRequestedDate, c.checkRequestedBy, c.claimInvoice, c.claimRef, c.readyToAudit, c.readyToClear,
cc.auditorUserId, cc.reconciled, cc.reconciledDate, cc.reconciledByUserId
update tbl_TPM_ActivityStats
set claimType = ,
claimDate = ,
reconciled = ,
reconciledDate = ,
reconciledByUserId = ,
auditorUserId = ,
preparerUserId = ,
checkRequested = ,
checkRequestedDate = ,
checkRequestedBy = ,
claimInvoice = ,
claimRef = ,
readyToAudit = ,
readyToClear = ,
subClaimCount = ,
lastUpdated =
where ownerId =
and ownerType =
and claimId =
select tas.claimId
from tbl_TPM_ActivityStats tas with (nolock)
where tas.ownerId =
and tas.ownerType =
and tas.claimId =
and tas.eventDetails =
update tbl_TPM_ActivityStats
set eventDetails = ,
lastUpdated =
where ownerId =
and ownerType =
and claimId =
select tas.claimId
from tbl_TPM_ActivityStats tas with (nolock)
where tas.ownerId =
and tas.ownerType =
and tas.claimId =
and tas.taskDetails =
update tbl_TPM_ActivityStats
set taskDetails = ,
lastUpdated =
where ownerId =
and ownerType =
and claimId =