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 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, c.claimAmount claimTotal, c.volumeBased, c.partnerType claimPartnerType, c.partnerId claimPartnerId, c.claimRef, c.clearingTs, c.claimType, c.readyToAudit, mp.email as processorEmail, 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 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_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 order by sectionRank, contractNbr for json path, include_null_values