Hi all,
I would like to generate in a SQL query records starting from a specific date for the missing date ranges.
Here is the sample dataset:
WITH v
AS
(
SELECT 1 as CUST_ID,
to_date('30.05.2022', 'DD.MM.YYYY') as DATE_FROM,
to_date('29.05.2023', 'DD.MM.YYYY') as DATE_TO
FROM DUAL
UNION ALL
SELECT 1 as CUST_ID,
to_date('30.04.2023', 'DD.MM.YYYY') as DATE_FROM,
to_date('29.04.2024', 'DD.MM.YYYY') as DATE_TO
FROM DUAL
UNION ALL
SELECT 1 as CUST_ID,
to_date('01.07.2024', 'DD.MM.YYYY') as DATE_FROM,
to_date('31.12.2024', 'DD.MM.YYYY') as DATE_TO
FROM DUAL
UNION ALL
SELECT 1 as CUST_ID,
to_date('30.05.2025', 'DD.MM.YYYY') as DATE_FROM,
to_date('29.09.2025', 'DD.MM.YYYY') as DATE_TO
FROM DUAL
UNION ALL
SELECT 2 as CUST_ID,
to_date('01.01.2022', 'DD.MM.YYYY') as DATE_FROM,
to_date('30.05.2023', 'DD.MM.YYYY') as DATE_TO
FROM DUAL
UNION ALL
SELECT 2 as CUST_ID,
to_date('28.02.2023', 'DD.MM.YYYY') as DATE_FROM,
to_date('27.02.2024', 'DD.MM.YYYY') as DATE_TO
FROM DUAL
UNION ALL
SELECT 2 as CUST_ID,
to_date('01.04.2025', 'DD.MM.YYYY') as DATE_FROM,
to_date('31.12.2025', 'DD.MM.YYYY') as DATE_TO
FROM DUAL
)
SELECT CUST_ID, DATE_FROM, DATE_TO
FROM v
ORDER BY CUST_ID, DATE_FROM
;
CUST_ID DATE_FROM DATE_TO
======= ========== ==========
1 30.05.2022 29.05.2023
1 30.04.2023 29.04.2024
1 01.07.2024 31.12.2024
1 30.05.2025 29.09.2025
2 01.01.2022 30.05.2023
2 28.02.2023 27.02.2024
2 01.04.2025 31.12.2025
Starting date is to_date('01.01.2022', 'DD.MM.YYYY')
Enddate is last day of the current year.
So I need to get following additional new dateranges (marked with asterisk):
CUST_ID DATE_FROM DATE_TO
======= ========== ==========
1 01.01.2022 29.05.2022 *
1 30.05.2022 29.05.2023
1 30.04.2023 29.04.2024
1 30.04.2024 30.06.2024 *
1 01.07.2024 31.12.2024
1 01.01.2025 29.05.2025 *
1 30.05.2025 29.09.2025
1 30.09.2025 31.12.2025 *
2 01.01.2022 30.05.2023
2 28.02.2023 27.02.2024
2 28.02.2024 31.03.2025 *
2 01.04.2025 31.12.2025
How can I do it?
Thank you very much!
Best regards!