Re: Recursive CTE

On 12/29/18 12:34 PM, Glenn Schultz wrote:
> 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

If you are trying to find month summaries, then maybe date_trunc would be easier:

select date_trunc('month', 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
group by date_trunc('month', fctrdt)