By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tableA (id varchar(10), descr varchar(10), eff_date date);
insert into tableA values ('A', 'XXX', '2019/6/10');
insert into tableA values ('A', 'YYY', '2019/6/13');
insert into tableA values ('B', 'ZZZ', '2019/6/15');
select * from tableA;
| id | descr | eff_date |
|---|---|---|
| A | XXX | 2019-06-10 |
| A | YYY | 2019-06-13 |
| B | ZZZ | 2019-06-15 |
with a as (
select a.id, a.descr, a.eff_date,
dateadd(day, -1, lead(a.eff_date, 1, '2019-06-18') over (partition by id order by eff_date)) as end_date
from tablea a
),
cte as (
select id, descr, eff_date, end_date
from a
union all
select id, descr, dateadd(day, 1, eff_date), end_date
from cte
where eff_date < end_date
)
select id, descr, eff_date
from cte
order by id, eff_date
| id | descr | eff_date |
|---|---|---|
| A | XXX | 2019-06-10 |
| A | XXX | 2019-06-11 |
| A | XXX | 2019-06-12 |
| A | YYY | 2019-06-13 |
| A | YYY | 2019-06-14 |
| A | YYY | 2019-06-15 |
| A | YYY | 2019-06-16 |
| A | YYY | 2019-06-17 |
| B | ZZZ | 2019-06-15 |
| B | ZZZ | 2019-06-16 |
| B | ZZZ | 2019-06-17 |