SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table PaymentSchedules
(
  auto_numbered int identity(1,1) primary key,
  res_id varchar(30),
  date_start date,
  date_end date,
  org varchar(30),
  pct decimal(3,0)
)
insert into PaymentSchedules
(res_id, org, pct, date_start, date_end)
values
  ('A', 'One',   100, '2018-01-01', '2018-06-30')
, ('A', 'One',   100, '2019-01-01', '2019-06-30')
, ('A', 'One',    60, '2019-07-01', null)
, ('A', 'Two',    40, '2019-07-02', '2019-12-31')
, ('A', 'Two',    40, '2020-01-01', null)
, ('B', 'Three', 100, null,         null)
, ('C', 'One',   100, '2018-01-01', null)
, ('C', 'Four',  100, '2019-11-01', null)
;
8 rows affected

declare @MaxEndDate date;
set @MaxEndDate = (select max(iif(date_start > date_end, date_start, isnull(date_end, date_start))) from PaymentSchedules);

;with rcte as
(
  select res_id
  , datefromparts(year(min(date_start)), month(min(date_start)), 1) as month_start
  , eomonth(coalesce(max(date_end), @MaxEndDate)) as month_end
  , 0 as lvl
  from PaymentSchedules
  group by res_id
  having min(date_start) is not null
  
  union all
  
  select res_id
  , dateadd(month, 1, month_start)
  , month_end
  , lvl + 1
  from rcte
  where dateadd(month, 1, month_start) < month_end
)
, cte_gai as
(
select c.res_id, c.month_start, c.month_end
, t.org, t.pct, t.auto_numbered
, sum(isnull(t.pct,0)) over (partition by c.res_id, c.month_start) as res_month_pct
, count(t.auto_numbered) over (partition by c.res_id, c.month_start) as cnt
from rcte c
left join PaymentSchedules t
  on t.res_id = c.res_id
 and c.month_start >= datefromparts(year(t.date_start), month(t.date_start), 1)
 and c.month_start <= coalesce(t.date_end, @MaxEndDate)
)
select *
from cte_gai
where res_month_pct <> 100
order by res_id, month_start


res_id
month_start
month_end
org
pct
auto_numbered
res_month_pct
cnt


A
2018-07-01
2019-12-31
null
null
null
0
0

A
2018-08-01
2019-12-31
null
null
null
0
0

A
2018-09-01
2019-12-31
null
null
null
0
0

A
2018-10-01
2019-12-31
null
null
null
0
0

A
2018-11-01
2019-12-31
null
null
null
0
0

A
2018-12-01
2019-12-31
null
null
null
0
0

C
2019-11-01
2020-01-31
One
100
7
200
2

C
2019-11-01
2020-01-31
Four
100
8
200
2

C
2019-12-01
2020-01-31
One
100
7
200
2

C
2019-12-01
2020-01-31
Four
100
8
200
2

C
2020-01-01
2020-01-31
One
100
7
200
2

C
2020-01-01
2020-01-31
Four
100
8
200
2


Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.

res_id	month_start	month_end	org	pct	auto_numbered	res_month_pct	cnt
A	2018-07-01	2019-12-31	null	null	null	0	0
A	2018-08-01	2019-12-31	null	null	null	0	0
A	2018-09-01	2019-12-31	null	null	null	0	0
A	2018-10-01	2019-12-31	null	null	null	0	0
A	2018-11-01	2019-12-31	null	null	null	0	0
A	2018-12-01	2019-12-31	null	null	null	0	0
C	2019-11-01	2020-01-31	One	100	7	200	2
C	2019-11-01	2020-01-31	Four	100	8	200	2
C	2019-12-01	2020-01-31	One	100	7	200	2
C	2019-12-01	2020-01-31	Four	100	8	200	2
C	2020-01-01	2020-01-31	One	100	7	200	2
C	2020-01-01	2020-01-31	Four	100	8	200	2