with claimDays1 as ( select *, datediff( day, coalesce( lastClaim, termStart ), getDate() ) daysSinceLastClaim from tbl_DW_AgreementAnalyzer_#_tableSuffix# with (nolock) where ownerId = and ownerType = and agreementStatus = 'active' and requestModel != 'SPENDING' and territoryId in ( ) and accountOwnerUserId = ), claimDays as ( select case when daysSinceLastClaim > 180 then 'inactive' when daysSinceLastClaim > 120 then 'delinquent' when daysSinceLastClaim > 90 then 'lagging' else 'active' end as claimState, partnerId, partnerType, partnerTk, partnerName, assignedContractNbr, contractName, format( termStart, 'MM/dd/yyyy' ) termStart, format( termEnd, 'MM/dd/yyyy' ) termEnd, format( lastClaim, 'MM/dd/yyyy' ) lastClaim, territoryName, coalesce( accountOwnerUserId, 0 ) accountOwnerUserId, accountOwnerFirstName, accountOwnerLastName, accountOwnerEmail, coalesce( territoryId, 0 ) territoryId, contractId, case when lastClaim is null then -1 else daysSinceLastClaim end as daysSinceLastClaim from claimDays1 ) select * from claimDays where claimState = order by territoryName, partnerName