I want to join two table all_clients and data_db with two columns: client_key and period; Then calculate sum statistics for each clients and period from all_clients table. Minimum period of all_clients is '2020/05', however when I left join it to data_db resulted table has minimum period 2019/08. Indeed, resulted table (df2) should have minimum period equals to '2020/05'; what is error? code of joining is below:

create table empl_df_bog as 
       with df1 as (select c.compinn
               ,c.client_key
               ,c.period
               ,max((case when nvl(s.dpd_max,0) > 90 then 1 else 0 end)) is_default 
               from all_clients c
               left join data_db s on c.client_key = s.client_key and 
               s.year_month between to_char(add_months(to_date(c.period,'YYYY/MM'), -12),'yyyy/mm') 
               and to_char(add_months(to_date(c.period,'YYYY/MM'), -1),'yyyy/mm')
               group by c.compinn, c.client_key, c.period)
,          df2 as(select compinn
              ,period
              ,avg(is_default) DR
              from df1
              group by compinn, period)
              select * from df2

CodePudding user response：

What about applying the same date / character conversion to the s.year_month field? Does that give you the intended results? I find when converting dates using TO_CHAR I have to do that with both dates in this case s.year_month.

After setting that up what does the results look like in your first CTE df1?