declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @claimId int = ; declare @contractNbr varchar(25) = ; update tbl_TPM_Claims set clearingTs = #getTickCount()# where ownerId = @ownerId and ownerType = @ownerType and claimId = @claimId and clearingTs is null; with data as ( select distinct pod.flagged, cc.ownerId, cc.ownerType, cc.claimId, cc.claimContractId, case when ct.partnerType = 'OPR' then oao.companyName when ct.partnerType = 'CDR' then dao.cdr_dstName else cast( null as varchar ) end as beneficiaryName, r.assignedContractNbr, r.requestId, r.description requestDescription, r.payableTo, cc.firstDate, cc.lastDate, cc.contractNbr, cc.status, case when cc.contractNbr != '$MISCELLANEOUS' then cc.subClaimAmount else cc.miscAmount end subClaimAmount, cc.prepareComment claimContractPrepareComment, cc.statusComment claimContractStatusComment, cc.reconciled, cc.reconciledDate, format( cc.firstDate, 'MM/dd/yyyy' ) as firstDateFormatted, format( cc.lastDate, 'MM/dd/yyyy' ) as lastDateFormatted, ma.email as auditorEmail, ma.firstName as auditorFirstName, ma.lastName as auditorLastName, c.claimAmount claimTotal, c.volumeBased, c.partnerType claimPartnerType, c.partnerId claimPartnerId, c.claimRef, c.clearingTs, c.claimType, c.readyToAudit, c.claimStatusDate, c.claimStatusUserId, mp.email as processorEmail, lu.firstName as lastUpdatedFirstName, lu.lastName as lastUpdatedLastName, DateFromParts( year( firstDate ), month( firstDate ), 1 ) purchasingMonthStart, DateFromParts( year( lastDate ), month( lastDate ), 1 ) purchasingMonthEnd, datediff( month, DateFromParts( year( firstDate ), month( firstDate ), 1 ), DateFromParts( year( lastDate ), month( lastDate ), 1 )) + 1 purchasingMonths, case when cc.contractNbr != '$MISCELLANEOUS' then 0 else 1 end sectionRank, row_number() over ( partition by cc.claimContractId order by case when pod.flagged = 'Y' then 0 else 1 end ) as row_rank from tbl_TPM_ClaimContracts cc with (nolock) left outer join tbl_TPM_ProofOfDelivery pod with (nolock) on pod.claimId = cc.claimId and pod.ownerId = cc.ownerId and pod.ownerType = cc.ownerType and pod.contractNbr = cc.contractNbr inner join tbl_TPM_Claims c with (nolock) on c.ownerId = cc.ownerId and c.ownerType = cc.ownerType and c.claimId = cc.claimId left outer join tbl_Fspro_members mp with (nolock) on mp.ownerId = c.ownerId and mp.fsl_tablecode = c.ownerType and mp.fspro_userId = c.processorUserId left outer join tbl_Fspro_members ma with (nolock) on ma.ownerId = cc.ownerId and ma.fsl_tablecode = cc.ownerType and ma.fspro_userId = cc.auditorUserId left outer join tbl_FSPRO_Members lu with (nolock) on lu.ownerId = c.ownerId and lu.fsl_tablecode = c.ownerType and lu.fspro_userId = c.claimStatusUserId left outer join tbl_TPM_Requests r with (nolock) on r.ownerId = cc.ownerId and r.ownerType = cc.ownerType and r.requestId = cc.requestId left outer join tbl_TPM_Contracts ct with (nolock) on ct.ownerId = r.ownerId and ct.ownerType = r.ownerType and ct.contractId = r.contractId left outer join tbl_OPR_CLientOperators oao with (nolock) on oao.operatorId = ct.partnerId and 'OPR' = ct.partnerType and oao.ownerId = ct.ownerId and oao.fsltablecode = ct.ownerType left outer join tbl_CDR_Distributors dao with (nolock) on dao.cdr_recordid = ct.partnerId and 'CDR' = ct.partnerType and dao.ownerId = ct.ownerId and dao.fsl_tablecode = ct.ownerType where cc. ownerId = @ownerId and cc.ownerType = @ownerType and cc.claimId = @claimId and cc.contractNbr != '$MISCELLANEOUS' and cc.contractNbr = @contractNbr ) select * from data where row_rank = 1 order by sectionRank, contractNbr for json path, include_null_values