Hello All,
Can you please suggest me on below query;
I have below table t1.
With t1
AS
(
Select 'AK' Name, '01-JUN-18' Service_enroll_st_dt, '01-NOV-18' Service_dt, '30-NOV-26' Service_enroll_end_dt from DUAL
)
select * from t1;
Scenario1:
NAME SERVICE_ENROLL_ST_DT SERVICE_DT SERVICE_ENROLL_END_DT
AK 01-JUN-18 01-NOV-18 30-NOV-26
--expected result
NAME Service_dt Service_yr
-----------------------
AK 01-NOV-18 NOV-2018
AK 01-NOV-18 NOV-2019
AK 01-NOV-18 NOV-2020
AK 01-NOV-18 NOV-2021
AK 01-NOV-18 NOV-2022
AK 01-NOV-18 NOV-2023
AK 01-NOV-18 NOV-2024
AK 01-NOV-18 NOV-2025
AK 01-NOV-18 NOV-2026
Scenario2:
with t1
AS
(
Select 'AK' Name, '01-JUN-18' Service_enroll_st_dt, '01-NOV-18' Service_dt, '30-NOV-20' Service_enroll_end_dt from DUAL
UNION ALL
Select 'AK' Name, '01-JAN-21' Service_enroll_st_dt, '01-JUL-21' Service_dt, '30-NOV-26' Service_enroll_end_dt from DUAL
)
select * from t1;
--------
NAME SERVICE_ENROLL_ST_DT SERVICE_DT SERVICE_ENROLL_END_DT
AK 01-JUN-18 01-NOV-18 30-NOV-20
AK 01-JAN-21 01-JUL-21 30-NOV-26
--expected result
NAME Service_dt Service_yr
-----------------------
AK 01-NOV-18 NOV-2018
AK 01-NOV-18 NOV-2019
AK 01-NOV-20 NOV-2020
AK 01-JUL-21 JUL-2021
AK 01-JUL-21 JUL-2022
AK 01-JUL-21 JUL-2023
AK 01-JUL-21 JUL-2024
AK 01-JUL-21 JUL-2025
AK 01-JUL-21 JUL-2026
Scenario3:
NAME SERVICE_ENROLL_ST_DT SERVICE_DT SERVICE_ENROLL_END_DT
AK 01-JUN-18 01-NOV-18 30-NOV-20
AK 01-JAN-21 01-JUL-20 30-NOV-24
AK 01-FEB-25 01-AUG-25 30-NOV-26
--expected result
NAME Service_dt Service_yr
-----------------------
AK 01-NOV-18 NOV-2018
AK 01-NOV-18 NOV-2019
AK 01-NOV-20 NOV-2020
AK 01-JUL-21 JUL-2021
AK 01-JUL-21 JUL-2022
AK 01-JUL-21 JUL-2023
AK 01-JUL-21 JUL-2024
AK 01-AUG-25 AUG-2025
AK 01-AUG-25 AUG-2026
Thank you for your time.