Hi All,
I want to populate the gaps between min date and max date of c_date for each person. The following query can do it.However, it generates the whole data set. I want to generate rows between missing date gap rows only.
Version: 19C
with
t1 as
(
SELECT 'Chris' c_name, to_date('022020', 'MMYYYY') c_date FROM dual
union all
SELECT 'Chris', to_date('032020', 'MMYYYY') FROM dual
union all
SELECT 'Chris', to_date('062020', 'MMYYYY') FROM dual
union all
SELECT 'Chris', to_date('072020', 'MMYYYY') FROM dual
union all
SELECT 'Chris', to_date('082020', 'MMYYYY') FROM dual
union all
SELECT 'Lisa', to_date('052020', 'MMYYYY') FROM dual
union all
SELECT 'Lisa', to_date('062020', 'MMYYYY') FROM dual
union all
SELECT 'Lisa', to_date('102020', 'MMYYYY') FROM dual
union all
SELECT 'Lisa', to_date('112020', 'MMYYYY') FROM dual
union all
SELECT 'Lisa', to_date('122020', 'MMYYYY') FROM dual
),
t2 as (
SELECT add_months(to_date('01/01/2020', 'dd/mm/yyyy'), level-1) DT FROM dual connect by level <= 12
)
SELECT * FROM t2 left join t1 partition by (t1.c_name) on t2.dt = t1.c_date;
Intended output:
C_NAME DT C_DATE
------ ---------- ----------
Chris 01/02/2020 01/02/2020
Chris 01/03/2020 01/03/2020
Chris 01/04/2020
Chris 01/05/2020
Chris 01/06/2020 01/06/2020
Chris 01/07/2020 01/07/2020
Chris 01/08/2020 01/08/2020
Lisa 01/05/2020 01/05/2020
Lisa 01/06/2020 01/06/2020
Lisa 01/07/2020
Lisa 01/08/2020
Lisa 01/09/2020
Lisa 01/10/2020 01/10/2020
Lisa 01/11/2020 01/11/2020
Lisa 01/12/2020 01/12/2020
Thanks in advance.