Home » SQL & PL/SQL » SQL & PL/SQL » overlap data need (Oracle Database 11g Express Edition Release 11.2.0.2.0, Win 7)
overlap data need [message #587660] Mon, 17 June 2013 18:25 Go to next message
shumail
Messages: 76
Registered: September 2012
Location: Canada
Member
Hi All

I already raised overlap issue in my last post but this problem is different than my previous one. I also tried to perform it through hierarchy query but unable to create logic. I have the following data:
Data
WITH
      DATA AS
              (
                SELECT '100' GRP, '01-JAN-2012' EFFECTIVE_DATE, '31-JAN-2012' TERMINATION_DATE from DUAL -- Valid
                union all
                SELECT '100' GRP, '15-JAN-2012' EFFECTIVE_DATE, '30-JAN-2012' FROM DUAL -- Invalid -- overlap (between the duration of row no 1 effective and termination date)
                union all
                SELECT '100' GRP, '31-JAN-2012' EFFECTIVE_DATE, '05-FEB-2012' FROM DUAL -- Invalid -- overlap (31-JAN-2012 already exist on row no 1)
                union all
                select '100' grp, '01-JAN-2012' effective_date, '31-JAN-2012' termination_date from dual -- Invalid record - same recod exist in row no 1 
                union all
                select '100' grp, '15-JAN-2012' effective_date, '30-JAN-2012' from dual -- Invalid -- overlap
                union all
                select '100' grp, '03-JUL-2012' effective_date, '05-AUG-2012' from dual -- Valid -- Not overlap and gap is allow
                union all
                SELECT '100' GRP, '06-AUG-2012' EFFECTIVE_DATE, '15-AUG-2012' FROM DUAL -- Valid -- Not overlap and gap is allow
                union all
                select '100' grp, '17-AUG-2012' effective_date, null from dual-- Valid -- becaue no overlap in effective date and termination date=null is ok
                union all
                select '200' grp, '01-JUL-2012' effective_date, '31-JUL-2012' from dual--Valid
                union all
                select '200' grp, '28-JUL-2012' effective_date, '05-AUG-2012' from dual-- Invalid -- overlap
                union all
                select '200' grp, '06-AUG-2012' EFFECTIVE_DATE, '15-AUG-2012' FROM DUAL -- Valid
                ),
     correct_data as (
      select grp,
             to_date(effective_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH') effective_date,
             to_date(termination_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH') termination_date
      from data
     )
     select grp, effective_date, termination_date
    from correct_data
     ;


Expected output
100	15-JAN-12	30-JAN-12
100	31-JAN-12	05-FEB-12
100	01-JAN-12	31-JAN-12
100	15-JAN-12	30-JAN-12
200	28-JUL-12	05-AUG-12


I need all invalid record. Please guide
Re: overlap data need [message #587661 is a reply to message #587660] Mon, 17 June 2013 20:24 Go to previous messageGo to next message
BlackSwan
Messages: 22712
Registered: January 2009
Senior Member
Why do you allow invalid records to get into the table in the first place?
If the input values would result in an invalid record, then do not proceed with the INSERT & therefore no need to have to struggle to remove invalid records.
Re: overlap data need [message #587662 is a reply to message #587661] Mon, 17 June 2013 20:30 Go to previous messageGo to next message
shumail
Messages: 76
Registered: September 2012
Location: Canada
Member
Hi
I'm working on business rule and according to rule I have to load invalid records into one of the log table. These data come from old system at the time of loading data...
Re: overlap data need [message #587663 is a reply to message #587662] Mon, 17 June 2013 20:46 Go to previous messageGo to next message
BlackSwan
Messages: 22712
Registered: January 2009
Senior Member
First make it work, then make it fancy.

Post one simple SQL statement that correctly identifies all records that violate any single rule.
Post as many simple SQL statements as there are different rules or criteria to identify invalid records.
Re: overlap data need [message #588205 is a reply to message #587660] Sun, 23 June 2013 10:16 Go to previous messageGo to next message
shumail
Messages: 76
Registered: September 2012
Location: Canada
Member
Hi All

Finally I have got all my possible combination. Please find the below details.

Insertion of data
create table emp_period
(emp_no number(10),
proj_no number(10),
effective_date date,
termination_date date);

-----------------
insert into emp_period
values
(1,100,to_date('01-JAN-2012'),to_date('10-JAN-2012')
);

insert into emp_period
values
(2,100,to_date('10-JAN-2012'),to_date('12-JAN-2012')
);

insert into emp_period
values
(3,100,to_date('14-JAN-2012'),to_date('17-JAN-2012')
);

insert into emp_period
values
(4,100,to_date('16-JAN-2012'),to_date('20-JAN-2012')
);

insert into emp_period
values
(5,100,to_date('22-JAN-2012'),to_date('30-JAN-2012')
);

insert into emp_period
values
(6,100,to_date('23-JAN-2012'),to_date('26-JAN-2012')
);

insert into emp_period
values
(7,100,to_date('01-FEB-2012'),to_date('10-FEB-2012')
);

insert into emp_period
values
(8,100,to_date('01-FEB-2012'),to_date('09-FEB-2012')
);

insert into emp_period
values
(9,100,to_date('01-FEB-2012'),to_date('15-FEB-2012')
);

insert into emp_period
values
(10,100,to_date('01-MAR-2012'),to_date('09-MAR-2012')
);

insert into emp_period
values
(11,100,to_date('01-MAR-2012'),to_date('10-MAR-2012')
);

insert into emp_period
values
(12,100,to_date('01-MAR-2012'),to_date('15-MAR2012')
);

insert into emp_period
values
(13,100,to_date('17-MAR-2012'),to_date('20-MAR-2012')
);

insert into emp_period
values
(14,100,to_date('17-MAR-2012'),to_date('20-MAR-2012')
);

insert into emp_period
values
(15,100,to_date('01-APR-2012'),to_date('15-APR-2012')
);

insert into emp_period
values
(16,100,to_date('01-APR-2012'),to_date('09-APR-2012')
);

insert into emp_period
values
(17,100,to_date('01-APR-2012'),to_date('10-APR-2012')
);

insert into emp_period
values
(18,200,to_date('01-JAN-2012'),to_date('10-JAN-2012')
);

insert into emp_period
values
(19,100,to_date('14-JAN-2012'),to_date('17-JAN-2012')
);

insert into emp_period
values
(20,100,to_date('22-JAN-2012'),to_date('30-JAN-2012')
);

Query from table
Select * FROM emp_period

Query Result
EMP_NO PROJ_NO  EFFECTIVE_DATE  TERMINATION_DATE
1	100	01-JAN-12	10-JAN-12
2	100	10-JAN-12	12-JAN-12
3	100	14-JAN-12	17-JAN-12
4	100	16-JAN-12	20-JAN-12
5	100	22-JAN-12	30-JAN-12
6	100	23-JAN-12	26-JAN-12
7	100	01-FEB-12	10-FEB-12
8	100	01-FEB-12	09-FEB-12
9	100	01-FEB-12	15-FEB-12
10	100	01-MAR-12	09-MAR-12
11	100	01-MAR-12	10-MAR-12
12	100	01-MAR-12	15-MAR-12
13	100	17-MAR-12	20-MAR-12
14	100	17-MAR-12	20-MAR-12
15	100	01-APR-12	15-APR-12
16	100	01-APR-12	09-APR-12
17	100	01-APR-12	10-APR-12
18	200	01-JAN-12	10-JAN-12
19	100	14-JAN-12	17-JAN-12
20	100	22-JAN-12	30-JAN-12

Expected Result
EMP_NO PROJ_NO  EFFECTIVE_DATE  TERMINATION_DATE
2	100	10-JAN-12	12-JAN-12 --overlap because effective date is smaller than termination_Date of emp_no=01
4	100	16-JAN-12	20-JAN-12 --overlap because effective date is smaller than termination_Date of emp_no=03
6	100	23-JAN-12	26-JAN-12 --overlap because effective date is smaller than termination_Date of emp_no=05
8	100	01-FEB-12	09-FEB-12 --overlap because effective date is smaller than termination_Date of emp_no=07
9	100	01-FEB-12	15-FEB-12 --overlap because effective date is smaller than termination_Date of emp_no=08
11	100	01-MAR-12	10-MAR-12 --overlap because effective date is smaller than termination_Date of emp_no=10
12	100	01-MAR-12	15-MAR-12 --overlap because effective date is smaller than termination_Date of emp_no=11
14	100	17-MAR-12	20-MAR-12 --overlap because if the previous record contained same Proj_no, effective and termination date  then system consider first enter record that Emp_no=13 not overlap because it enter first in database (emp_no 13 is enter first and then emp_no 14 and emp_no is a sequence)
16	100	01-APR-12	09-APR-12 --overlap 
17	100	01-APR-12	10-APR-12 --overlap 


Summary is that I need all overlap records and if the all previous and current records are same then system consider 2nd record overlap because it enters later and we can judge this thing by emp_no field that is sequence. Please let me know if you have any questions. Thanks in advance.
Re: overlap data need [message #588206 is a reply to message #588205] Sun, 23 June 2013 10:43 Go to previous messageGo to next message
BlackSwan
Messages: 22712
Registered: January 2009
Senior Member
SQL> create table emp_period
(emp_no number(10),
proj_no number(10),
effective_date date,
termination_date date);  2    3    4    5  

Table created.

SQL> set term on echo on
SQL> insert into emp_period
values
(1,100,to_date('01-JAN-2012'),to_date('10-JAN-2012')
);  2    3    4  
(1,100,to_date('01-JAN-2012'),to_date('10-JAN-2012')
               *
ERROR at line 3:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL> 




using TO_DATE without any mask is useless & silly!
Re: overlap data need [message #588220 is a reply to message #588206] Sun, 23 June 2013 23:06 Go to previous messageGo to next message
shumail
Messages: 76
Registered: September 2012
Location: Canada
Member
Hi

When I executed my insertion script then I'm not getting any error but this time I'm adding mask as well. Thanks

Insertion
insert into emp_period
values
(1,100,to_date('01-JAN-2012','DD-MON-YYYY'),to_date('10-JAN-2012','DD-MON-YYYY')
);

insert into emp_period
values
(2,100,to_date('10-JAN-2012','DD-MON-YYYY'),to_date('12-JAN-2012','DD-MON-YYYY')
);

insert into emp_period
values
(3,100,to_date('14-JAN-2012','DD-MON-YYYY'),to_date('17-JAN-2012','DD-MON-YYYY')
);

insert into emp_period
values
(4,100,to_date('16-JAN-2012','DD-MON-YYYY'),to_date('20-JAN-2012','DD-MON-YYYY')
);

insert into emp_period
values
(5,100,to_date('22-JAN-2012','DD-MON-YYYY'),to_date('30-JAN-2012','DD-MON-YYYY')
);

insert into emp_period
values
(6,100,to_date('23-JAN-2012','DD-MON-YYYY'),to_date('26-JAN-2012','DD-MON-YYYY')
);

insert into emp_period
values
(7,100,to_date('01-FEB-2012','DD-MON-YYYY'),to_date('10-FEB-2012','DD-MON-YYYY')
);

insert into emp_period
values
(8,100,to_date('01-FEB-2012'),to_date('09-FEB-2012','DD-MON-YYYY')
);

insert into emp_period
values
(9,100,to_date('01-FEB-2012','DD-MON-YYYY'),to_date('15-FEB-2012','DD-MON-YYYY')
);

insert into emp_period
values
(10,100,to_date('01-MAR-2012','DD-MON-YYYY'),to_date('09-MAR-2012','DD-MON-YYYY')
);

insert into emp_period
values
(11,100,to_date('01-MAR-2012','DD-MON-YYYY'),to_date('10-MAR-2012','DD-MON-YYYY')
);

insert into emp_period
values
(12,100,to_date('01-MAR-2012','DD-MON-YYYY'),to_date('15-MAR2012','DD-MON-YYYY')
);

insert into emp_period
values
(13,100,to_date('17-MAR-2012','DD-MON-YYYY'),to_date('20-MAR-2012','DD-MON-YYYY')
);

insert into emp_period
values
(14,100,to_date('17-MAR-2012','DD-MON-YYYY'),to_date('20-MAR-2012','DD-MON-YYYY')
);

insert into emp_period
values
(15,100,to_date('01-APR-2012','DD-MON-YYYY'),to_date('15-APR-2012','DD-MON-YYYY')
);

insert into emp_period
values
(16,100,to_date('01-APR-2012','DD-MON-YYYY'),to_date('09-APR-2012','DD-MON-YYYY')
);

insert into emp_period
values
(17,100,to_date('01-APR-2012','DD-MON-YYYY'),to_date('10-APR-2012','DD-MON-YYYY')
);

insert into emp_period
values
(18,200,to_date('01-JAN-2012','DD-MON-YYYY'),to_date('10-JAN-2012','DD-MON-YYYY')
);

insert into emp_period
values
(19,100,to_date('14-JAN-2012','DD-MON-YYYY'),to_date('17-JAN-2012','DD-MON-YYYY')
);

insert into emp_period
values
(20,100,to_date('22-JAN-2012','DD-MON-YYYY'),to_date('30-JAN-2012','DD-MON-YYYY')
);


Regards

Shumail
Re: overlap data need [message #588299 is a reply to message #588220] Mon, 24 June 2013 13:34 Go to previous messageGo to next message
shumail
Messages: 76
Registered: September 2012
Location: Canada
Member
Really appreciate if someone solve my issue. Thanks
Re: overlap data need [message #590223 is a reply to message #588299] Mon, 15 July 2013 23:26 Go to previous message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11gR2> select * from emp_period order by emp_no
  2  /

    EMP_NO    PROJ_NO EFFECTIVE TERMINATI
---------- ---------- --------- ---------
         1        100 01-JAN-12 10-JAN-12
         2        100 10-JAN-12 12-JAN-12
         3        100 14-JAN-12 17-JAN-12
         4        100 16-JAN-12 20-JAN-12
         5        100 22-JAN-12 30-JAN-12
         6        100 23-JAN-12 26-JAN-12
         7        100 01-FEB-12 10-FEB-12
         8        100 01-FEB-12 09-FEB-12
         9        100 01-FEB-12 15-FEB-12
        10        100 01-MAR-12 09-MAR-12
        11        100 01-MAR-12 10-MAR-12
        12        100 01-MAR-12 15-MAR-12
        13        100 17-MAR-12 20-MAR-12
        14        100 17-MAR-12 20-MAR-12
        15        100 01-APR-12 15-APR-12
        16        100 01-APR-12 09-APR-12
        17        100 01-APR-12 10-APR-12
        18        200 01-JAN-12 10-JAN-12
        19        100 14-JAN-12 17-JAN-12
        20        100 22-JAN-12 30-JAN-12

20 rows selected.

SCOTT@orcl_11gR2> select emp_no, proj_no, effective_date, termination_date
  2  from   (select emp_period.*,
  3  		    lag (termination_date) over (order by emp_no) lag_term_date,
  4  		    lag (effective_date) over (order by emp_no) lag_eff_date
  5  	     from   emp_period)
  6  where  effective_date <= lag_term_date
  7  and    termination_date >= lag_eff_date
  8  order  by emp_no
  9  /

    EMP_NO    PROJ_NO EFFECTIVE TERMINATI
---------- ---------- --------- ---------
         2        100 10-JAN-12 12-JAN-12
         4        100 16-JAN-12 20-JAN-12
         6        100 23-JAN-12 26-JAN-12
         8        100 01-FEB-12 09-FEB-12
         9        100 01-FEB-12 15-FEB-12
        11        100 01-MAR-12 10-MAR-12
        12        100 01-MAR-12 15-MAR-12
        14        100 17-MAR-12 20-MAR-12
        16        100 01-APR-12 09-APR-12
        17        100 01-APR-12 10-APR-12

10 rows selected.

Previous Topic: Global Temporary Table and Autonomous_Transaction
Next Topic: Trigger for INSERT, UPDATE OR DELETE
Goto Forum:
  


Current Time: Thu Aug 28 03:02:53 CDT 2014

Total time taken to generate the page: 0.07510 seconds