All,
Following my earlier post on variable instantiation, I rethought how I was working with dates and realized I can fix the date and use static interval. I came up with this recursive CTE which is the end goal. However, the problem is that the convexity query cannot be used as a subquery. So I think I need to use a join of convexity on the original query - not sure I am little stuck at this point but I feel I am close. Any help would be appreciated.
-Glenn
SET max_parallel_workers_per_gather = 8;
SET random_page_cost = 1;
SET enable_partitionwise_aggregate = on;
with recursive convexity (fctrdt, CPR3mo) as
(
select
cast((select max(fctrdt) - interval '1 month' from fnmloan) as date) as "fctrdt"
,round(
smmtocpr(
cast(
sum(currrpb * case when fnmloan.fctrdt = fnmloan_data.fctrdt then fnmloan_data.event else 0 end)/ sum(currrpb)
as numeric) * 100
),4) * 100 as "CPR 3mo"
from
fnmloan
join
fnmloan_data
on
fnmloan_data.loanseqnum = fnmloan.loanseqnum
where
fnmloan.fctrdt between ((select max(fctrdt)-interval '1 month' from fnmloan) - interval '2 month') and (select max(fctrdt) - interval '1 month' from fnmloan)
and
fnmloan.poolprefix = 'CL'
union all
select
cast((select max(fctrdt) - interval '1 month' from convexity) as date) as "fctrdt"
,round(
smmtocpr(
cast(
sum(currrpb * case when fnmloan.fctrdt = fnmloan_data.fctrdt then fnmloan_data.event else 0 end)/ sum(currrpb)
as numeric) * 100
),4) * 100 as "CPR 3mo"
from
fnmloan
join
fnmloan_data
on
fnmloan_data.loanseqnum = fnmloan.loanseqnum
where
fnmloan.fctrdt between ((select max(fctrdt)-interval '1 month' from convexity) - interval '2 month') and (select max(fctrdt) - interval '1 month' from convexity)
and
fnmloan.poolprefix = 'CL'
and
convexity.fctrdt <= (select max(fctrdt) - interval' 12 months' from fnmloan)
)
select * from convexity
