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, 353, 155, 251, 496, 20, 230414 ) -- Compass, Foodbuy, Aramark, IPS - Aramark, HPSI, Premier, Vizient, Avendra and Hilton
),
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 ( '00288000000241', '00288000000266', '00288000000225', '00288000000290', '00288000000282', '00288000000308', '00288000000324', '00288000000233', '00288000000274' ) -- 9 Agreements (Each Per Client)
and c.claimDate >= '01/01/2022'
)
select * from claimsToUnClear
order by cast( claimDate as datetime ), claimId
Done