This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,901 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Need help with Query

User910243567
User910243567 Member Posts: 623 Silver Badge
edited Nov 22, 2022 7:07PM in SQL & PL/SQL

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.

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,213 Red Diamond
    edited Nov 23, 2022 10:20PM 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.

    User910243567

Answers

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond

    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.

  • User910243567
    User910243567 Member Posts: 623 Silver Badge
    edited Nov 23, 2022 9:36PM

    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
    
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,213 Red Diamond
    edited Nov 23, 2022 10:20PM 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.

    User910243567
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond

    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
    ;
    
    
    
    User910243567