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 businessOwnerUserId =
),
claimDays as (
select case when daysSinceLastClaim > 180 then 'inactive'
when daysSinceLastClaim > 120 then 'delinquent'
when daysSinceLastClaim > 90 then 'lagging'
else 'active' end as claimState, daysSinceLastClaim, contractId, 1 as c
from claimDays1
)
select 'Claim Activity' as argument, sum( inactive ) inactive, sum( delinquent ) delinquent, sum( lagging ) lagging
from ( select *
from claimDays
pivot( sum( c )
for claimState in ( inactive, delinquent, lagging )
) p
) x