Home » SQL & PL/SQL » SQL & PL/SQL » Finding Date overlap (Oracle 11 g)
Finding Date overlap [message #665561] Mon, 11 September 2017 04:10 Go to next message
samiran_cts
Messages: 52
Registered: January 2012
Member
Hi Sir,

I have to find date overlap between active and inactive records.
I have a table Party
create table party(
party_id number,
alt_id number,
start_dt date,
end_date date,
Active  Char(1));

insert into party 
values (100,1000,'3-FEB-16','4-MAY-16','N');	
insert into party 
values (100,1001,'2-JAN-16','31-DEC-19','Y');	
insert into party 
values (200,1011,'3-FEB-16','4-MAY-16','N');	
insert into party 
values (200,1052,'2-APR-16','31-DEC-19','Y');	

insert into party 
values (300,2052,'2-APR-16','31-DEC-19','Y');

I need the result as:
OLD_PARTY OLD_ALT HIS_ST_DT HIS_END_DT NEW_PARTY NEW_ALT CURR_ST_DT CUR_END_DT OVERLAP_EXP_DT
100 1000 03-FEB-16 04-MAY-16 100 1001 02-JAN-16 31-DEC-19 -123
200 1011 03-FEB-16 04-MAY-16 200 1052 02-APR-16 31-DEC-19 -32

I have used this query to get the result :
 select * from
   ( select a.*,b.*,b.curr_st_dt - a.his_end_dt overlap_exp_dt  from	
	(select party_id old_party,Alt_id old_alt,Start_dt his_st_dt, end_date his_end_dt from
	(select a.*, 
		row_number() over(partition by party_id order by Start_dt ) r
		from Party  a
		where active ='N' )) a,	
( select party_id new_party,Alt_id new_alt,Start_dt curr_st_dt, end_date cur_end_dt from
	(select a.*, 
		row_number() over(partition by party_id order by Start_dt ) r
		from Party  a
		where active ='Y' )) b 
 where a.old_party =  b.new_party ) where overlap_exp_dt <= 0

Is there any alternative approach like lead or lag using to get this type of result. So that performance can improve.

Regards,
Samiran
Re: Finding Date overlap [message #665562 is a reply to message #665561] Mon, 11 September 2017 04:47 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Query using Lead fuction :


WITH DATA AS(
SELECT p.* ,
  LEAD(p.stART_DT , 1, NULL) OVER ( PARTITION BY PARTY_ID ORDER BY START_DT )NEXT_START_DT,
  LEAD(p.END_DATE , 1, NULL) OVER ( PARTITION BY PARTY_ID ORDER BY START_DT )NEXT_END_DATE
FROM party p 
WHERE START_DT <END_DATE)
SELECT D.* ,
  CASE WHEN  NEXT_START_DT BETWEEN  START_DT AND END_DATE  AND  NEXT_END_DATE BETWEEN  START_DT AND END_DATE THEN  
 NEXT_END_DATE- NEXT_START_DT
 ELSE  
 END_DATE-NEXT_START_DT
 END  OVERLAP  FROM DATA D
WHERE NEXT_START_DT BETWEEN  START_DT AND END_DATE;
Re: Finding Date overlap [message #665567 is a reply to message #665562] Mon, 11 September 2017 05:47 Go to previous messageGo to next message
samiran_cts
Messages: 52
Registered: January 2012
Member
Thank you, Sir, I modified the query to get the alt id:
WITH DATA AS(
SELECT p.* ,
LEAD(p.stART_DT , 1, NULL) OVER ( PARTITION BY PARTY_ID ORDER BY START_DT )NEXT_START_DT,
LEAD(p.END_DATE , 1, NULL) OVER ( PARTITION BY PARTY_ID ORDER BY START_DT )NEXT_END_DATE,
lead(p.alt_id,1,null) OVER ( PARTITION BY PARTY_ID ORDER BY START_DT )NEXT_ALTID
FROM party p
WHERE START_DT <END_DATE)
SELECT D.* ,
CASE WHEN NEXT_START_DT BETWEEN START_DT AND END_DATE AND NEXT_END_DATE BETWEEN START_DT AND END_DATE THEN
NEXT_END_DATE- NEXT_START_DT
ELSE
END_DATE-NEXT_START_DT
END OVERLAP FROM DATA D
WHERE NEXT_START_DT BETWEEN START_DT AND END_DATE;

[Updated on: Mon, 11 September 2017 06:08]

Report message to a moderator

Re: Finding Date overlap [message #665584 is a reply to message #665561] Mon, 11 September 2017 08:33 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Hi,

As the combination of columns (PARTY_ID, ACTIVE) is always unique in sample data, the result set can be obtained by simple aggregation
select party_id old_party
  , max(case when active = 'N' then alt_id end) old_alt_id
  , max(case when active = 'N' then start_dt end) his_st_dt
  , max(case when active = 'N' then end_date end) his_end_dt
  , party_id new_party
  , max(case when active = 'Y' then alt_id end) new_alt_id
  , max(case when active = 'Y' then start_dt end) cur_st_dt
  , max(case when active = 'Y' then end_date end) cur_end_dt
  , max(case when active = 'Y' then start_dt end) - max(case when active = 'N' then end_date end) overlap_exp_dt
from party
group by party_id
having max(case when active = 'Y' then start_dt end) - max(case when active = 'N' then end_date end) < 0
;
If (assumption based on qualified guess) there is only one row with ACTIVE='Y' for each PARTY_ID, you may use analytics like this:
with part_data as (
select party_id old_party, alt_id old_alt_id, start_dt his_st_dt, end_date his_end_dt
  , active, party_id new_party
  , max(case when active = 'Y' then alt_id end) over (partition by party_id) new_alt_id
  , max(case when active = 'Y' then start_dt end) over (partition by party_id) cur_st_dt
  , max(case when active = 'Y' then end_date end) over (partition by party_id) cur_end_dt
  , max(case when active = 'Y' then start_dt end) over (partition by party_id) - end_date overlap_exp_dt
from party
)
select * from part_data
where active = 'N' and overlap_exp_dt < 0
;
Otherwise, you are probably out of luck - you have to join rows with the same PARTY_ID and different ACTIVE values, which leads to (partial) cartesian product.

As even saipradyumn's query (which does not use ACTIVE column at all) returns the expected result, I wonder what are exact rules for its obtaining.
Re: Finding Date overlap [message #665615 is a reply to message #665584] Tue, 12 September 2017 09:39 Go to previous messageGo to next message
quirks
Messages: 82
Registered: October 2014
Member
I just wonder what speaks against a good old selfjoin?
WITH
    PARTY(PARTY_ID
         ,ALT_ID
         ,START_DT
         ,END_DATE
         ,ACTIVE)
    AS
        (SELECT 100, 1000, TO_DATE('03.02.2016', 'DD.MM.YYYY'), TO_DATE('04.05.2016', 'DD.MM.YYYY'), 'N' FROM DUAL
         UNION ALL
         SELECT 100, 1001, TO_DATE('02.02.2016', 'DD.MM.YYYY'), TO_DATE('31.12.2019', 'DD.MM.YYYY'), 'Y' FROM DUAL
         UNION ALL
         SELECT 200, 1011, TO_DATE('03.02.2016', 'DD.MM.YYYY'), TO_DATE('04.05.2016', 'DD.MM.YYYY'), 'N' FROM DUAL
         UNION ALL
         SELECT 200, 1052, TO_DATE('02.04.2016', 'DD.MM.YYYY'), TO_DATE('31.12.2019', 'DD.MM.YYYY'), 'Y' FROM DUAL
         UNION ALL
         SELECT 300, 2052, TO_DATE('02.04.2016', 'DD.MM.YYYY'), TO_DATE('31.12.2019', 'DD.MM.YYYY'), 'Y' FROM DUAL)
SELECT OLD_RECORD.PARTY_ID                       AS OLD_PARTY
      ,OLD_RECORD.ALT_ID                         AS OLD_ALT
      ,OLD_RECORD.START_DT                       AS HIS_ST_DT
      ,OLD_RECORD.END_DATE                       AS HIS_END_DT
      ,NEW_RECORD.PARTY_ID                       AS NEW_PARTY
      ,NEW_RECORD.ALT_ID                         AS NEW_ALT
      ,NEW_RECORD.START_DT                       AS CURR_ST_DT
      ,NEW_RECORD.END_DATE                       AS CURR_END_DT
      ,OLD_RECORD.END_DATE - NEW_RECORD.START_DT AS OVERLAP
  FROM PARTY OLD_RECORD, PARTY NEW_RECORD
 WHERE OLD_RECORD.PARTY_ID = NEW_RECORD.PARTY_ID
   AND OLD_RECORD.ACTIVE = 'N'
   AND NEW_RECORD.ACTIVE = 'Y'
   AND OLD_RECORD.END_DATE > NEW_RECORD.START_DT
   AND OLD_RECORD.START_DT < NEW_RECORD.END_DATE;

If you want the overlap to be a negative number just subtract the END_DATE from the START_DT.

[Updated on: Tue, 12 September 2017 09:43]

Report message to a moderator

Re: Finding Date overlap [message #665617 is a reply to message #665615] Tue, 12 September 2017 09:58 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
Another way to do it in a single pass is to use a pipelined function.

CREATE OR REPLACE TYPE OVERLAP AS OBJECT
( party_ID1 number,
  alt_id1 number,
  start_date1 date,
  end_date1 date,
  active1 char(1),
  party_ID2 number,
  alt_id2 number,
  start_date2 date,
  end_date2 date,
  active2 char(1),
  gap number);
/

CREATE OR REPLACE TYPE OVERLAP_TABLE AS TABLE OF OVERLAP;
/

CREATE OR REPLACE FUNCTION OVERLAP_GET RETURN OVERLAP_TABLE
PIPELINED IS
  vOverLap OVERLAP := OVERLAP(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
  vPrevParty NUMBER := -1;
  vPrevAltID NUMBER;
  vPrevStart DATE;
  vPrevEnd DATE;
  vPrevActive CHAR(1);
BEGIN
   for c1 in (select * from party order by party_id, ALT_ID) LOOP
      IF vPrevParty = c1.PARTY_ID THEN
         IF c1.START_DT < vPrevEnd THEN -- Overlap
            vOverLap.party_ID1     := vPrevParty;
            vOverLap.alt_id1       := vPrevAltID;
            vOverLap.start_date1   := vPrevStart;
            vOverLap.end_date1     := vPrevEnd;
            vOverLap.active1       := vPrevActive;
            vOverLap.party_ID2     := c1.PARTY_ID;
            vOverLap.alt_id2       := c1.ALT_ID;
            vOverLap.start_date2   := c1.START_DT;
            vOverLap.end_date2     := c1.END_DATE;
            vOverLap.active2       := c1.ACTIVE;
            vOverLap.gap           := c1.START_DT - vPrevEnd;
            
            PIPE ROW(vOverLap);
         END IF;
      END IF;

      vPrevParty             := c1.PARTY_ID;
      vPrevAltID             := c1.ALT_ID;
      vPrevStart             := c1.START_DT;
      vPrevEnd               := c1.END_DATE;
      vPrevActive            := c1.ACTIVE;
      
   END LOOP;
   RETURN;
END;
/

SELECT * FROM TABLE(OVERLAP_GET);

 PARTY_ID1    ALT_ID1 START_DAT END_DATE1 A  PARTY_ID2    ALT_ID2 START_DAT END_DATE2 A        GAP
---------- ---------- --------- --------- - ---------- ---------- --------- --------- - ----------
       100       1000 03-FEB-16 04-MAY-16 N        100       1001 02-JAN-16 31-DEC-19 Y       -123
       200       1011 03-FEB-16 04-MAY-16 N        200       1052 02-APR-16 31-DEC-19 Y        -32

Re: Finding Date overlap [message #665619 is a reply to message #665617] Wed, 13 September 2017 01:17 Go to previous messageGo to next message
samiran_cts
Messages: 52
Registered: January 2012
Member
Thank all of you . I think Self join will impact the performance, because there is millions of records in the table. I will check with the pipeline function.

Regards,
Samiran
Re: Finding Date overlap [message #665620 is a reply to message #665619] Wed, 13 September 2017 02:17 Go to previous message
quirks
Messages: 82
Registered: October 2014
Member
Since (if i remember right) Oracle 11c self joins are processed by reading the table only once. So it should be (please prove me wrong) quite fast.

While fiddling around with your data I figured that my result is a little bit different from your desired one (even if the result of your own statement matches mine). The overlap for Party_ID is expected as -123 but you and I get (-)92. Which is right? And if -123 then how do you calculate it (I suspect this happens if you get the value from the previous row without looking at the PARTY_ID)?

If you don't want to write a function an love to complicate things you could use a recursive statement as well. This one reads the table only once, but you might need oracle 12c (for earlier versions you could try connect by prior):
WITH
    PARTY(PARTY_ID
         ,ALT_ID
         ,START_DT
         ,END_DATE
         ,ACTIVE)
    AS
        (SELECT 100, 1000, TO_DATE('03.02.2016', 'DD.MM.YYYY'), TO_DATE('04.05.2016', 'DD.MM.YYYY'), 'N' FROM DUAL
         UNION ALL
         SELECT 100, 1001, TO_DATE('02.02.2016', 'DD.MM.YYYY'), TO_DATE('31.12.2019', 'DD.MM.YYYY'), 'Y' FROM DUAL
         UNION ALL
         SELECT 200, 1011, TO_DATE('03.02.2016', 'DD.MM.YYYY'), TO_DATE('04.05.2016', 'DD.MM.YYYY'), 'N' FROM DUAL
         UNION ALL
         SELECT 200, 1052, TO_DATE('02.04.2016', 'DD.MM.YYYY'), TO_DATE('31.12.2019', 'DD.MM.YYYY'), 'Y' FROM DUAL
         UNION ALL
         SELECT 300, 2052, TO_DATE('02.04.2016', 'DD.MM.YYYY'), TO_DATE('31.12.2019', 'DD.MM.YYYY'), 'Y' FROM DUAL),
    T1(OLD_PARTY
      ,OLD_ALT
      ,HIS_ST_DT
      ,HIS_END_DT
      ,ACTIVE
      ,NEW_PARTY
      ,NEW_ALT
      ,CURR_ST_DT
      ,CURR_END_DT
      ,OVERLAP)
    AS
        (SELECT NULL     AS OLD_PARTY
               ,NULL     AS OLD_ALT
               ,NULL     AS HIS_ST_DT
               ,NULL     AS HIS_END_DT
               ,ACTIVE   AS ACTIVE
               ,PARTY_ID AS NEW_PARTY
               ,ALT_ID   AS NEW_ALT
               ,START_DT AS CURR_ST_DT
               ,END_DATE AS CURR_END_DT
               ,NULL     AS OVERLAP
           FROM PARTY
          WHERE ACTIVE = 'N'
         UNION ALL
         -- Recursive member.
         SELECT ROOT.NEW_PARTY                 AS OLD_PARTY
               ,ROOT.NEW_ALT                   AS OLD_ALT
               ,ROOT.CURR_ST_DT                AS HIS_ST_DT
               ,ROOT.CURR_END_DT               AS HIS_END_DT
               ,T2.ACTIVE                      AS ACTIVE
               ,T2.PARTY_ID                    AS NEW_PARTY
               ,T2.ALT_ID                      AS NEW_ALT
               ,T2.START_DT                    AS CURR_ST_DT
               ,T2.END_DATE                    AS CURR_END_DT
               ,ROOT.CURR_END_DT - T2.START_DT AS OVERLAP
           FROM PARTY T2, T1 ROOT
          WHERE ROOT.CURR_END_DT > T2.START_DT AND ROOT.CURR_ST_DT <= T2.END_DATE AND T2.ACTIVE = 'Y' AND ROOT.NEW_PARTY = T2.PARTY_ID)
        SEARCH DEPTH FIRST BY CURR_ST_DT SET SORT_ORDER
        CYCLE NEW_PARTY SET CYCLE TO '1' DEFAULT '0'
SELECT OLD_PARTY
      ,OLD_ALT
      ,HIS_ST_DT
      ,HIS_END_DT
      ,NEW_PARTY
      ,NEW_ALT
      ,CURR_ST_DT
      ,CURR_END_DT
      ,OVERLAP
  FROM T1
 WHERE ACTIVE = 'Y';

Previous Topic: UNIQUE CONSTRAINT FOR EXISTING COLUMN WITH DUPLICATE RECORDS
Next Topic: UTL_FILE.PUT. Need help to handle the line with more than 32767 characters
Goto Forum:
  


Current Time: Thu Mar 28 08:35:56 CDT 2024