Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Need help with Query

User910243567Nov 22 2022 — edited Nov 22 2022

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.

This post has been answered by Solomon Yakobson on Nov 23 2022
Jump to Answer

Comments

Processing

Post Details

Added on Nov 22 2022
4 comments
217 views