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

Categories

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:
· Share on TwitterShare on Facebook

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
    · Share on TwitterShare on Facebook

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.

    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook