Home » SQL & PL/SQL » SQL & PL/SQL » Get ovelap (Oracle, Oracle Database 11g Express Edition Release 11.2.0.2.0 ,Win 7 Pro)
Get ovelap [message #603450] Mon, 16 December 2013 12:36 Go to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
Hi All
Need your help in order to find overlap records. I have the following structure.
DROP TABLE age_ckh;

CREATE TABLE age_ckh
(
min_age NUMBER(3),
max_age NUMBER(3),
rule_id NUMBER(5),
bill_id NUMBER(5),
status_code VARCHAR2(10)
);

INSERT INTO age_ckh
(
SELECT 17, 33 ,1, 18,'COU' FROM dual
UNION ALL
SELECT 34, 43 ,1, 19,'COU' FROM dual
UNION ALL
SELECT 44, 53 ,1, 20,'COU' FROM dual
UNION ALL
SELECT 54, 58 ,1, 21,'COU' FROM dual
UNION ALL
SELECT 59, 64 ,1, 22,'COU' FROM dual
UNION ALL
SELECT 52, 55 ,1, 23,'COU' FROM dual--Wrong
UNION ALL
SELECT 50, 52 ,1, 24,'FAM' FROM dual--Wrong
UNION ALL
SELECT 17, 33 ,1, 25,'FAM' FROM dual
UNION ALL
SELECT 34, 43 ,1, 26,'FAM' FROM dual
UNION ALL
SELECT 44, 53 ,1, 27,'FAM' FROM dual
UNION ALL
SELECT 54, 58 ,1, 28,'FAM' FROM dual
UNION ALL
SELECT 59, 64 ,1, 29,'FAM' FROM dual
UNION ALL
SELECT 62, 64 ,1, 29,'FAM' FROM dual--WRONG
UNION ALL
SELECT 17, 33 ,1, 30,'SIN' FROM dual
UNION ALL
SELECT 34, 43 ,1, 31,'SIN' FROM dual
UNION ALL
SELECT 44, 53 ,1, 32,'SIN' FROM dual
UNION ALL
SELECT 54, 58 ,1, 33,'SIN' FROM dual
UNION ALL
SELECT 59, 64 ,1, 34,'SIN' FROM dual
UNION ALL
SELECT 17, 33 ,1, 35,'COU' FROM dual
UNION ALL
SELECT 34, 43 ,1, 36,'COU' FROM dual
UNION ALL
SELECT 44, 53 ,1, 37,'COU' FROM dual
UNION ALL
SELECT 54, 58 ,1, 38,'COU' FROM dual
UNION ALL
SELECT 59, 64 ,1, 39,'COU' FROM dual
UNION ALL
SELECT 60, 61 ,1, 40,'COU' FROM dual--WRONG
);
COMMIT;


Need the following output
Min_age   Max_age   Rule_ID   Bill_ID   Status_code
52	          55	      1	        23	      COU
50	          52	      1	        24	      FAM
62	          64	      1	        29	      FAM
60	          61	      1	        40	      COU


Rules
- Overlap not allow excluding if min_age = other min_age or max_age = other max_age


Please let me know if you have any question, Really appreciate your reply. Thanks

Shumail
Re: Get ovelap [message #603456 is a reply to message #603450] Mon, 16 December 2013 13:51 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
I really have no clue, but what have you allready came up with?
Re: Get ovelap [message #603461 is a reply to message #603456] Mon, 16 December 2013 14:07 Go to previous messageGo to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
let me explain it in more details.
I need the following output
Min_age           Max_age   Rule_ID   Bill_ID   Status_code
52	          55	      1	        23	      COU--1st Row
50	          52	      1	        24	      FAM--2nd Row
62	          64	      1	        29	      FAM--3rd row
60	          61	      1	        40	      COU--4th Row

If you see 1st row then its Min_age = 52 and Max_age=55 and these row range is between any previous min_age and max_age row range like bill_id=22
If you see 2nd row then its Min_age = 50 and Max_age=52 and these row range is between any previous min_age and max_age row range like bill_id=20
If you see 3nd row then its Min_age = 62 and Max_age=64 and these row range is between any previous min_age and max_age row range like bill_id=29
If you see 4th row then its Min_age = 60 and Max_age=61 and these row range is between any previous min_age and max_age row range like bill_id=39


Summary is that if any record Min_age and Max_age range is between any previous rows Min_age and Max_age range then system return that row but if any record Min_age and Max_age range = any previous rows min_age and max_age then exclude that row

[Updated on: Mon, 16 December 2013 14:11]

Report message to a moderator

Re: Get ovelap [message #603464 is a reply to message #603461] Mon, 16 December 2013 14:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
rows in a table are like balls in a basket.
Which ball in the basket is the first ball.
rows in a table have NO inherent order.
Re: Get ovelap [message #603465 is a reply to message #603464] Mon, 16 December 2013 14:25 Go to previous message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
Thanks for the reply, order should be bill_id and its a primary key as well.
Previous Topic: Materialized View on Remote DB
Next Topic: how to get last week end date ?
Goto Forum:
  


Current Time: Fri Mar 29 02:09:08 CDT 2024