Home » SQL & PL/SQL » SQL & PL/SQL » Trying to Report on Date Periods within a table (Oracle 12C)
Trying to Report on Date Periods within a table [message #676701] Sat, 06 July 2019 15:48 Go to next message
mvs2019
Messages: 5
Registered: July 2019
Junior Member
I have a table that has several sets of dates. I am trying to generate a resultset that shows distinct time frames in which
the customer either had or different aid categories:

Table Cust
Customer_Nbr    EFF_DATE    	END_DATE     AID_CATG     CATG_EFF_DATE	 CATG_END_DATE
000001		01/01/2012      12/31/2014	A1	   04/01/2012	 04/30/2012
000001		01/01/2012      12/31/2019	A2	   06/01/2012	 08/31/2012
000002		01/02/2012      12/31/2019	A3	   01/02/2012	 08/31/2012
..

Resultset I am trying to produce
Customer_Nbr    EFF_DATE    	END_DATE     AID_CATG      
000001		01/01/2012	03/31/2012
000001		04/01/2012	04/30/2012    A1
000001		05/01/2012	05/31/2012
000001		06/01/2012	08/31/2012    A2
000001		09/01/2012	08/31/2019     
I am looking at using analytical functions to make it scalable.

[Updated on: Sat, 06 July 2019 16:00] by Moderator

Report message to a moderator

Re: Trying to Report on Date Periods within a table [message #676702 is a reply to message #676701] Sat, 06 July 2019 16:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Trying to Report on Date Periods within a table [message #676703 is a reply to message #676701] Sun, 07 July 2019 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Are CATG_EFF_DATE and CATG_END_DATE between EFF_DATE and END_DATE in all rows?
In other words, are there the following constraints in your table definition:
EFF_DATE <= END_DATE
CATG_EFF_DATE <= CATG_END_DATE
CATG_EFF_DATE between EFF_DATE and END_DATE
CATG_END_DATE between EFF_DATE and END_DATE

Re: Trying to Report on Date Periods within a table [message #676704 is a reply to message #676703] Sun, 07 July 2019 15:42 Go to previous messageGo to next message
mvs2019
Messages: 5
Registered: July 2019
Junior Member
I have attached DDL, DML and what I have thus far.

create table table_1
(cust_no varchar2(11) not null,
cust_first_name varchar2(40),
cust_last_name varchar2(40),
cust_aid_catg_cd varchar2(10),
beg_elig_dt date not null,
end_elig_dt date);

create table table_2
(
person_no varchar2(11) not null,
ELIG_CD varchar2(10),
VALID_FR_DT DATE,
VALID_TO_DT DATE );



insert into table_1
(cust_no, cust_first_name, cust_last_name , cust_aid_catg_cd, beg_elig_dt, end_elig_dt)
select '0000000001','Robert','Johnson','52',to_date('09/01/2002','mm/dd/yyyy'),to_date('04/30/2011','mm/dd/yyyy') from dual;

insert into table_1
(cust_no, cust_first_name, cust_last_name , cust_aid_catg_cd , beg_elig_dt, end_elig_dt)
select '0000000001','Robert','Johnson','26',to_date('05/01/2011','mm/dd/yyyy'),to_date('04/30/2014','mm/dd/yyyy') from dual;


insert into table_1
(cust_no, cst_first_name, cust_last_name , cust_aid_catg_cd, beg_elig_dt, end_elig_dt)
select '0000000001','Robert','Johnson','52',to_date('05/01/2014','mm/dd/yyyy'),to_date('03/31/2015','mm/dd/yyyy') from dual;

insert into table_2 (person_no, elig_cd, VALID_FR_DT , VALID_TO_DT)
select '0000000001','99',to_date('02/07/2008','mm/dd/yyyy'),to_date('03/05/2008','mm/dd/yyyy') from dual;

insert into table_2(person_no, elig_cd, VALID_FR_DT , VALID_TO_DT)
select '0000000001','95',to_date('05/28/2011','mm/dd/yyyy'),to_date('11/19/2011','mm/dd/yyyy') from dual;

commit;


Objective:

Using information from Table_1 and Table_2 for a given Cust_NO value I am trying to report on distinct time periods in which the Cust_No has either values for table_2.ELIG_CD or table_1.cust_aid_catg_cd The CATG column in the resultset is populated
from the Table_1.cust_aid_catg_cd and table_2.ELIG_CD columns.

Approach taken so far

Select from Table_1 and perform an Outer_Join on Table_2 using the Cust_no column as the joining column

Notes:

There is a possibility that the VALID_FR_DT and VALID_TO_DT column values in Table_2 overlap for a given cust_no value.
The overall boundaries of the BEG_ELIG_DT, END_ELIG_DT columns in the resultset
are set by the values of the BEG_ELIG_DT and END_ELIG_DT column values of Table_1.




For the sample data the expected resultset should be:

cust_no cst_fisrt_name cust_last_name Beg_ELIG_DT End_ELIG_DT CATG
000000001 Robert Johson 9/1/2002 2/6/2008 52
000000001 Robert Johson 2/7/2008 3/5/2008 99
000000001 Robert Johson 3/6/2008 4/30/2011 52
000000001 Robert Johson 5/1/2011 5/27/2011 26
000000001 Robert Johson 5/28/2011 11/19/2011 95
000000001 Robert Johson 11/20/2011 4/30/2019 26
000000001 Robert Johson 5/1/2014 3/31/2015 52


What I have so far:



With Join_Query_1 as (
select a.cust_no,a.cust_first_name,a.cust_last_name,a.cust_aid_catg_cd,a.beg_elig_dt,a.end_elig_dt,b.elig_cd,b.valid_fr_dt,b.valid_to_dt
from table_1 a,table_2 b
where a.cust_no = b.person_no(+)
and a.End_Elig_Dt >= b.valid_fr_dt(+) and
a.BEG_Elig_Dt <= nvl(b.valid_to_dt(+),to_date('12/31/9999','mm/dd/yyyy'))
)
select * from Join_Query_1;

/*
CUST_NO CUST_FIRST_NAME CUST_LAST_NAME CUST_AID_CATG_CD BEG_ELIG_DT END_ELIG_DT ELIG_CD VALID_FR_DT VALID_TO_DT
----------- ---------------------------------------- ---------------------------------------- ---------------- -------------------- -------------------- ---------- -------------------- --------------------
0000000001 Robert Johnson 52 01-SEP-2002 00:00:00 30-APR-2011 00:00:00 99 07-FEB-2008 00:00:00 05-MAR-2008 00:00:00
0000000001 Robert Johnson 26 01-MAY-2011 00:00:00 30-APR-2014 00:00:00 95 28-MAY-2011 00:00:00 19-NOV-2011 00:00:00
0000000001 Robert Johnson 52 01-MAY-2014 00:00:00 31-MAR-2015 00:00:00

*/

I need to further break it down by individual time periods and possibly use unipivot to generate the rows with the further broken down time periods.

Re: Trying to Report on Date Periods within a table [message #676705 is a reply to message #676704] Sun, 07 July 2019 17:53 Go to previous messageGo to next message
mvs2019
Messages: 5
Registered: July 2019
Junior Member
To answer your questions:


Are CATG_EFF_DATE and CATG_END_DATE between EFF_DATE and END_DATE in all rows?
In other words, are there the following constraints in your table definition:
EFF_DATE <= END_DATE --> Yes
CATG_EFF_DATE <= CATG_END_DATE --> Yes
CATG_EFF_DATE between EFF_DATE and END_DATE --> Not necessarily -- am filtering using this criteria
CATG_END_DATE between EFF_DATE and END_DATE --> Not necessarily -- am filtering using this criteria

Re: Trying to Report on Date Periods within a table [message #676706 is a reply to message #676705] Sun, 07 July 2019 18:41 Go to previous messageGo to next message
mvs2019
Messages: 5
Registered: July 2019
Junior Member
Hopefully I described the problem clearly. I am trying to get the result set by factoring in the dates from Table_2 to further break down the time periods from the data in Table_1.
Re: Trying to Report on Date Periods within a table [message #676707 is a reply to message #676706] Sun, 07 July 2019 20:48 Go to previous message
mvs2019
Messages: 5
Registered: July 2019
Junior Member
I came up with a rudimentary way using a set of UNION statements:



With Join_Query_1 as (

select a.cust_no,a.cust_first_name,a.cust_last_name,a.cust_aid_catg_cd,a.beg_elig_dt,a.end_elig_dt,b.elig_cd,b.valid_fr_dt,b.valid_to_dt

from table_1 a,table_2 b

where a.cust_no = b.person_no(+)

and a.End_Elig_Dt >= b.valid_fr_dt(+) and

a.BEG_Elig_Dt <= nvl(b.valid_to_dt(+),to_date('12/31/9999','mm/dd/yyyy'))

)

, union_query as (

select CUST_NO,CUST_FIRST_NAME , CUST_LAST_NAME ,CUST_AID_CATG_CD,BEG_ELIG_DT, valid_fr_dt -1 END_ELIG_DT

from Join_query_1 where cust_aid_catg_cd is not null and elig_cd is not null

union

select CUST_NO,CUST_FIRST_NAME , CUST_LAST_NAME ,CUST_AID_CATG_CD,valid_to_dt+1 BEG_ELIG_DT, end_elig_dt

from Join_query_1 where cust_aid_catg_cd is not null and elig_cd is not null

union

select CUST_NO,CUST_FIRST_NAME , CUST_LAST_NAME ,elig_cd CUST_AID_CATG_CD,valid_fr_dt BEG_ELIG_DT, valid_to_dt END_ELIG_DT

from Join_query_1 where cust_aid_catg_cd is not null and elig_cd is not null

union

select CUST_NO,CUST_FIRST_NAME , CUST_LAST_NAME ,CUST_AID_CATG_CD,BEG_ELIG_DT,END_ELIG_DT

from Join_query_1 where cust_aid_catg_cd is not null and elig_cd is null

)

select * from union_query

order by cust_no,beg_elig_dt

/*



CUST_NO CUST_FIRST_NAME CUST_LAST_NAME CUST_AID_CATG_CD BEG_ELIG_DT END_ELIG_DT

----------- ---------------------------------------- ---------------------------------------- ---------------- -------------------- --------------------

0000000001 Robert Johnson 52 01-SEP-2002 00:00:00 06-FEB-2008 00:00:00

0000000001 Robert Johnson 99 07-FEB-2008 00:00:00 05-MAR-2008 00:00:00

0000000001 Robert Johnson 52 06-MAR-2008 00:00:00 30-APR-2011 00:00:00

0000000001 Robert Johnson 26 01-MAY-2011 00:00:00 27-MAY-2011 00:00:00

0000000001 Robert Johnson 95 28-MAY-2011 00:00:00 19-NOV-2011 00:00:00

0000000001 Robert Johnson 26 20-NOV-2011 00:00:00 30-APR-2014 00:00:00

0000000001 Robert Johnson 52 01-MAY-2014 00:00:00 31-MAR-2015 00:00:00



7 rows selected

I am sure there are better alternatives than what I came up with

[Updated on: Sun, 07 July 2019 20:55]

Report message to a moderator

Previous Topic: Automatic abropting the long running job
Next Topic: MAX(DECODE
Goto Forum:
  


Current Time: Thu Mar 28 04:12:15 CDT 2024