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
