Home » SQL & PL/SQL » SQL & PL/SQL » Finding Date overlap (Oracle 11 g)
Finding Date overlap [message #665561] |
Mon, 11 September 2017 04:10 |
|
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 #665567 is a reply to message #665562] |
Mon, 11 September 2017 05:47 |
|
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 |
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 |
|
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 |
|
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 #665620 is a reply to message #665619] |
Wed, 13 September 2017 02:17 |
|
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';
|
|
|
Goto Forum:
Current Time: Thu Mar 28 08:35:56 CDT 2024
|