Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Need help with Query

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.
Best Answer
-
All 3 "scenarios" fall under same solution. One possible solution is lateral/cross apply join:
with t1 as ( select 1 s_no,'AK' name,date '2018-06-01' service_enroll_st_dt,date '2018-11-01' last_service_dt,date '2020-11-30' service_enroll_end_dt from dual union all select 2 s_no,'AK' name,date '2021-01-01' service_enroll_st_dt,date '2021-07-01' last_service_dt,date '2026-11-30' service_enroll_end_dt from dual union all select 3 s_no,'AK' name,date '2025-02-01' service_enroll_st_dt,date '2025-08-01' last_service_dt,date '2028-11-30' service_enroll_end_dt from dual ) select t1.name, to_char(t1.last_service_dt,'DD-MON-YY') service_dt, to_char(add_months(t1.last_service_dt,l.offset),'MON-YYYY') service_yr from t1, lateral( select (level - 1) * 12 offset from dual connect by add_months(t1.last_service_dt,(level - 1) * 12) <= t1.service_enroll_end_dt ) l order by t1.s_no, add_months(t1.last_service_dt,l.offset) / 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-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 AK 01-AUG-25 AUG-2025 AK 01-AUG-25 AUG-2026 AK 01-AUG-25 AUG-2027 AK 01-AUG-25 AUG-2028 13 rows selected. SQL>
Question is if dates can overlap or have a gap. And if so, then what is expected result.
SY.
Answers
-
Hi, @User910243567
Thanks for posting the sample data and desired results. Don't forget to explain why you want those results from the given data; that is, say what your requirements are.
Select 'AK' Name, '01-JUN-18' Service_enroll_st_dt, '01-NOV-18' Service_dt, ...
Don't use strings (such as '01-JUN-18') for information about dates; use DATE columns instead.
-
Thank you for your response, I am working on database version Oracle Database 19c Enterprise Edition , Ver: 19.0.0.0.0
I have below requirement to track the service history of the customer.
Below is table with 4 columns
Table Name: t1 Columns -------- Customers name Service Enrollment Start Date Service Date Service Enrollment End Date
-- Scenario-1
With t1 AS ( Select 1 S_NO, 'AK' Name, to_date('2018/06/01','RRRR/MM/DD'), to_date('2018/11/01','RRRR/MM/DD') Last_Service_dt, to_date('2026/11/30','RRRR/MM/DD') Service_enroll_end_dt from DUAL ) select * from t1;
-- Expected Result
-- Need to list down all the years as below with service date until the service enrollment end date based on table t1 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
-- Scenario-2
With t1 AS ( Select 1 S_NO, 'AK' Name, to_date('2018/06/01','RRRR/MM/DD')Service_enroll_st_dt, to_date('2018/11/01','RRRR/MM/DD') Service_dt, to_date('2020/11/30','RRRR/MM/DD') Service_enroll_end_dt from DUAL UNION ALL Select 2 S_NO, 'AK' Name, to_date('2021/01/01','RRRR/MM/DD') Service_enroll_st_dt, to_date('2021/07/01','RRRR/MM/DD') Service_dt, to_date('2026/11/30','RRRR/MM/DD') Service_enroll_end_dt from DUAL ) select * from t1;
-- expected result
-- Need to list down all the years as below with service date until the 'service enrollment end date' with 'service date' column value as most recent 'service date' from table t1
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-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
-- Scenario-3
With t1 AS ( Select 1 S_NO, 'AK' Name, to_date('2018/06/01','RRRR/MM/DD')Service_enroll_st_dt, to_date('2018/11/01','RRRR/MM/DD') Service_dt, to_date('2020/11/30','RRRR/MM/DD') Service_enroll_end_dt from DUAL UNION ALL Select 2 S_NO, 'AK' Name, to_date('2021/01/01','RRRR/MM/DD') Service_enroll_st_dt, to_date('2021/07/01','RRRR/MM/DD') Service_dt, to_date('2024/11/30','RRRR/MM/DD') Service_enroll_end_dt from DUAL UNION ALL Select 3 S_NO, 'AK' Name, to_date('2025/02/01','RRRR/MM/DD') Service_enroll_st_dt, to_date('2025/08/01','RRRR/MM/DD') Service_dt, to_date('2028/11/30','RRRR/MM/DD') Service_enroll_end_dt from DUAL ) select * from t1;
-- expected result
-- Need to list down all the years as below with service date until the 'service enrollment end date' with 'service date' as most recent 'service date' from table t1
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-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 AK 01-AUG-25 AUG-2027 AK 01-AUG-25 AUG-2028
-
All 3 "scenarios" fall under same solution. One possible solution is lateral/cross apply join:
with t1 as ( select 1 s_no,'AK' name,date '2018-06-01' service_enroll_st_dt,date '2018-11-01' last_service_dt,date '2020-11-30' service_enroll_end_dt from dual union all select 2 s_no,'AK' name,date '2021-01-01' service_enroll_st_dt,date '2021-07-01' last_service_dt,date '2026-11-30' service_enroll_end_dt from dual union all select 3 s_no,'AK' name,date '2025-02-01' service_enroll_st_dt,date '2025-08-01' last_service_dt,date '2028-11-30' service_enroll_end_dt from dual ) select t1.name, to_char(t1.last_service_dt,'DD-MON-YY') service_dt, to_char(add_months(t1.last_service_dt,l.offset),'MON-YYYY') service_yr from t1, lateral( select (level - 1) * 12 offset from dual connect by add_months(t1.last_service_dt,(level - 1) * 12) <= t1.service_enroll_end_dt ) l order by t1.s_no, add_months(t1.last_service_dt,l.offset) / 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-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 AK 01-AUG-25 AUG-2025 AK 01-AUG-25 AUG-2026 AK 01-AUG-25 AUG-2027 AK 01-AUG-25 AUG-2028 13 rows selected. SQL>
Question is if dates can overlap or have a gap. And if so, then what is expected result.
SY.
-
Hi, @User910243567
I see; so service_enroll_st_dt has nothing to do with this problem.
Here's one way:
SELECT t1.name, t1.service_dt , TO_CHAR ( c.anniversary , 'MON-YYYY' , 'NLS_DATE_LANGUAGE=ENGLISH' -- if necessary ) AS service_yr FROM t1 CROSS APPLY ( SELECT ADD_MONTHS ( t1.service_dt , 12 * (LEVEL - 1) ) AS anniversary FROM dual CONNECT BY ADD_MONTHS ( t1.service_dt , 12 * (LEVEL - 1) ) <= t1.service_enroll_end_dt ) c ORDER BY t1.name, t1.service_dt , c.anniversary ;