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 |
|
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 #676704 is a reply to message #676703] |
Sun, 07 July 2019 15:42 |
|
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 #676707 is a reply to message #676706] |
Sun, 07 July 2019 20:48 |
|
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
|
|
|
Goto Forum:
Current Time: Mon Jun 10 06:36:24 CDT 2024
|