select renewalMonthsThreshold
from tbl_TPM_Settings with (nolock)
where ownerId =
and ownerType =
with expirationDays1 as (
select *, datediff( day, getDate(), agreementEndDate ) daysUntilExpiration
from tbl_DW_AgreementAnalyzer_#_tableSuffix# with (nolock)
where ownerId =
and ownerType =
and datediff( day, getDate(), agreementEndDate ) >= 0
and requestModel != 'SPENDING'
and renew = 'Y'
and datediff( month, termStart, dateadd( day, 1, termEnd )) >
and territoryId in ( )
and businessOwnerUserId =
),
expriationDays as (
select case when daysUntilExpiration < 30 then 'nearterm'
when daysUntilExpiration < 90 then 'midterm'
when daysUntilExpiration < 120 then 'longterm'
else 'active' end as expirationState, daysUntilExpiration, contractId, 1 as c
from expirationDays1
)
select 'Expiration Days' as argument, coalesce( sum( nearterm ), 0) nearterm, coalesce( sum( midterm ), 0 ) midterm, coalesce( sum( longterm ), 0 ) longterm
from ( select *
from expriationDays
pivot( sum( c )
for expirationState in ( nearterm, midterm, longterm )
) p
) x