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 ( 292, 612 )
),
claimsToUnClear as (
select c.ownerId, c.ownerType, c.claimId, cp.partnerName, format( c.claimDate, 'MM/dd/yyyy' ) as claimDate, c.claimAmount, format( c.reconciledDate, 'MM/dd/yyy' ) dateCleared
from tbl_TPM_Claims c with (nolock)
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 c.reconciledAmount is not null
and c.claimDate >= '01/16/2022'
)
select * from claimsToUnClear
order by cast( claimDate as datetime ), claimId
Done