declare @ownerId int = 288;
declare @ownerType varchar(max) = 'MFR';
with
claimPartners as (
select operatorId as partnerId, 'OPR' as partnerType, companyName as partnerName, ownerid, fsltablecode as ownerType
from tbl_OPR_ClientOperators with (nolock)
where ownerId = @ownerId
and fsltablecode = @ownerType
and fsl_choId in ( 88, 610, 15, 155, 251, 496 ) -- Compass, Foodbuy, Aramark, HPSI, Premier and Vizient
),
claimsToUnClear as (
select c.ownerId, c.ownerType, c.claimId, cp.partnerName, format( c.claimDate, 'MM/dd/yyyy' ) as claimDate
from tbl_TPM_Contracts k with (nolock)
inner join tbl_TPM_Requests r with (nolock)
on r.ownerType = k.ownerType
and r.ownerId = k.ownerId
and r.contractId = k.contractId
inner join tbl_TPM_ClaimContracts cc with (nolock)
on cc.ownerType = r.ownerType
and cc.ownerId = r.ownerId
and cc.requestId = r.requestId
inner join tbl_TPM_Claims c with (nolock)
on c.ownerType = cc.ownerType
and c.ownerId = cc.ownerId
and c.claimId = cc.claimId
inner join claimPartners cp with (nolock)
on cp.ownerId = c.ownerId
and cp.ownerType = c.ownerType
and cp.partnerId = c.partnerId
and cp.partnerType = c.partnerType
where k.ownerType = @ownerType
and k.ownerId = @ownerId
and k.assignedContractNbr in ( '00288000001587', '00288000001579', '00288000001611', '00288000001496', '00288000001595', '00288000001603' ) -- 6 Agreements (Each Per Client)
and c.claimDate >= '01/01/2023'
)
select * from claimsToUnClear
order by cast( claimDate as datetime ), claimId
Done