By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t(Client int, StartDate date, EndDate date);
insert into t select 1 , '1-Jan-22', null ;
insert into t select 2 , '1-Jan-22', '3-Jan-22';
insert into t select 3 , '3-Jan-22', null ;
insert into t select 4 , '4-Jan-22', '5-Jan-22';
insert into t select 5 , '4-Jan-22', '6-Jan-22';
insert into t select 6 , '7-Jan-22', '9-Jan-22';
6 rows affected
with dates as (
select top(9) Convert(date,DateAdd(day, -1 + Row_Number() over(order by (select null)), '20220101')) dt
from master.dbo.spt_values
)
select d.dt [Date], c.NumActive
from dates d
outer apply (
select Count(*) NumActive
from t
where d.dt >= t.StartDate and (d.dt <= t.EndDate or t.EndDate is null)
)c;
| Date | NumActive |
|---|---|
| 2022-01-01 | 2 |
| 2022-01-02 | 2 |
| 2022-01-03 | 3 |
| 2022-01-04 | 4 |
| 2022-01-05 | 4 |
| 2022-01-06 | 3 |
| 2022-01-07 | 3 |
| 2022-01-08 | 3 |
| 2022-01-09 | 3 |