Home » SQL & PL/SQL » SQL & PL/SQL » Need all invalid records (Oracle Database 11g Express Edition Release 11.2.0.2.0 -, Windows 7)
| Need all invalid records [message #586972] |
Wed, 12 June 2013 03:00  |
 |
shumail
Messages: 149
Registered: September 2012
Location: Canada
|
Senior Member |
|
|
Hi All
I have the following sample data:
Sample Data
WITH DATA AS
(
SELECT '100' GRP, '01-JAN-2012' EFFECTIVE_DATE, '30-JUN-2012' TERMINATION_DATE from DUAL
UNION ALL
SELECT '100' GRP, '01-JUL-2012' EFFECTIVE_DATE, '31-JUL-2012' FROM DUAL
union all
SELECT '100' GRP, '31-JUL-2012' EFFECTIVE_DATE, '05-AUG-2012' FROM DUAL
UNION ALL
SELECT '100' GRP, '01-AUG-2012' EFFECTIVE_DATE, '15-AUG-2012' FROM DUAL
union all
select '100' grp, '17-AUG-2012' effective_date, '31-AUG-2012' from dual
)
select * from data;
Query Result
100 01-JAN-2012 30-JUN-2012
100 01-JUL-2012 31-JUL-2012
100 31-JUL-2012 05-AUG-2012
100 01-AUG-2012 15-AUG-2012
100 17-AUG-2012 31-AUG-2012
Expected Output
100 31-JUL-2012 05-AUG-2012
100 17-AUG-2012 31-AUG-2012
The above mentioned output is produced by using the following business rules:
-- row no 1 = Valid records
-- row no 2 = effective date is 1 day after termination date of row no 1. Means valid records
-- row no 3 = effective date is equal to the row no 2 termination date -- Means invalid record
-- row no 4 = effective date is 1 day after termination date of row no 2 Means valid records
-- row no 5 = The gap between row no 4 termination date and row no 5 effective date is more than 1 day. Means record is invalid.
Query always compare effective date with previous valid record termination date by using the above mentioned condition and return result accordingly.
Summary is that there should be 1 day gap between termination date and next effective date as well as effective date
is always less than termination date in same rows. In addition, if record is invalid then next record check with previous valid record termination date.
My English is not good so if you have any problem to understand my scenario then please let me know. Thanks in advance
Regards
Shu
|
|
|
|
|
|
| Re: Need all invalid records [message #586979 is a reply to message #586977] |
Wed, 12 June 2013 03:46  |
 |
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> WITH
2 DATA AS
3 (
4 SELECT '100' GRP, '01-JAN-2012' EFFECTIVE_DATE, '30-JUN-2012' TERMINATION_DATE from DUAL
5 UNION ALL
6 SELECT '100' GRP, '01-JUL-2012' EFFECTIVE_DATE, '31-JUL-2012' FROM DUAL
7 union all
8 SELECT '100' GRP, '31-JUL-2012' EFFECTIVE_DATE, '05-AUG-2012' FROM DUAL
9 UNION ALL
10 SELECT '100' GRP, '01-AUG-2012' EFFECTIVE_DATE, '15-AUG-2012' FROM DUAL
11 union all
12 select '100' grp, '17-AUG-2012' effective_date, '31-AUG-2012' from dual
13 ),
14 correct_data as (
15 select grp,
16 to_date(effective_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH') effective_date,
17 to_date(termination_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH') termination_date
18 from data
19 )
20 select grp, effective_date, termination_date
21 from correct_data
22 minus
23 select grp, effective_date, termination_date
24 from correct_data
25 connect by prior termination_date = effective_date-1
26 start with (grp, effective_date)
27 in (select grp, min(effective_date) from correct_data group by grp)
28 order by 1, 2
29 /
GRP EFFECTIVE_DATE TERMINATION_DATE
--- ------------------- -------------------
100 31/07/2012 00:00:00 05/08/2012 00:00:00
100 17/08/2012 00:00:00 31/08/2012 00:00:00
2 rows selected.
regards
Michel
|
|
|
|
| Re: Need all invalid records [message #587140 is a reply to message #586979] |
Wed, 12 June 2013 12:52 |
|
shumail
Messages: 149
Registered: September 2012
Location: Canada
|
Senior Member |
|
|
Hi Michel
Thanks for the query but it will skip some invalid records if I add some more records for example:
Query
WITH
DATA AS
(
SELECT '100' GRP, '01-JAN-2012' EFFECTIVE_DATE, '30-JUN-2012' TERMINATION_DATE from DUAL
union all
SELECT '100' GRP, '02-JAN-2012' EFFECTIVE_DATE, '29-JUN-2012' FROM DUAL -- Invalid record
union all
select '100' grp, '01-JUL-2012' effective_date, '05-AUG-2012' from dual
union all
SELECT '100' GRP, '01-AUG-2012' EFFECTIVE_DATE, '15-AUG-2012' FROM DUAL-- Invalid record
union all
select '100' grp, '17-AUG-2012' effective_date, '31-AUG-2012' from dual-- Invalid record
union all
select '200' grp, '01-JUL-2012' effective_date, '31-JUL-2012' from dual
union all
select '200' grp, '28-JUL-2012' effective_date, '05-AUG-2012' from dual--Invalid record
),
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
minus
select grp, effective_date, termination_date
from correct_data
connect by prior termination_date = effective_date-1
start with (grp, effective_date)
in (select grp, min(effective_date) from correct_data group by grp)
order by 1, 2;
Query shows the following result
100 02-JAN-12 29-JUN-12
100 17-AUG-12 31-AUG-12
200 28-JUL-12 05-AUG-12
In above mentioned data, I have one more invalid record that is '01-AUG-2012' EFFECTIVE_DATE and '15-AUG-2012' TERMINATION_DATE but query is not showing that record. Please advice
|
|
|
|
| Re: Need all invalid records [message #587144 is a reply to message #587140] |
Wed, 12 June 2013 13:24 |
|
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
This is the problem with not complete test case.
Just a missing test in "connect by":
SQL> WITH
2 DATA AS
3 (
4 SELECT '100' GRP, '01-JAN-2012' EFFECTIVE_DATE, '30-JUN-2012' TERMINATION_DATE from DUAL
5 union all
6 SELECT '100' GRP, '02-JAN-2012' EFFECTIVE_DATE, '29-JUN-2012' FROM DUAL -- Invalid record
7 union all
8 select '100' grp, '01-JUL-2012' effective_date, '05-AUG-2012' from dual
9 union all
10 SELECT '100' GRP, '01-AUG-2012' EFFECTIVE_DATE, '15-AUG-2012' FROM DUAL-- Invalid record
11 union all
12 select '100' grp, '17-AUG-2012' effective_date, '31-AUG-2012' from dual-- Invalid record
13 union all
14 select '200' grp, '01-JUL-2012' effective_date, '31-JUL-2012' from dual
15 union all
16 select '200' grp, '28-JUL-2012' effective_date, '05-AUG-2012' from dual--Invalid record
17 ),
18 correct_data as (
19 select grp,
20 to_date(effective_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH') effective_date,
21 to_date(termination_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH') termination_date
22 from data
23 )
24 select grp, effective_date, termination_date
25 from correct_data
26 minus
27 select grp, effective_date, termination_date
28 from correct_data
29 connect by prior termination_date = effective_date-1
30 and prior grp = grp
31 start with (grp, effective_date)
32 in (select grp, min(effective_date) from correct_data group by grp)
33 order by 1, 2
34 /
GRP EFFECTIVE_DATE TERMINATION_DATE
--- ------------------- -------------------
100 02/01/2012 00:00:00 29/06/2012 00:00:00
100 01/08/2012 00:00:00 15/08/2012 00:00:00
100 17/08/2012 00:00:00 31/08/2012 00:00:00
200 28/07/2012 00:00:00 05/08/2012 00:00:00
4 rows selected.
Regards
Michel
|
|
|
|
| Re: Need all invalid records [message #587150 is a reply to message #587144] |
Wed, 12 June 2013 14:08 |
|
shumail
Messages: 149
Registered: September 2012
Location: Canada
|
Senior Member |
|
|
Hi Michel
Thanks for the amazing query. I just check my last scenario (sorry for incomplete test case) and put duplicate effective date but system ignore that record for example:
WITH
DATA AS
(
SELECT '100' GRP, '01-JAN-2012' EFFECTIVE_DATE, '30-JUN-2012' TERMINATION_DATE from DUAL
union all
SELECT '100' GRP, '02-JAN-2012' EFFECTIVE_DATE, '29-JUN-2012' FROM DUAL -- Invalid record
union all
select '100' grp, '01-JUL-2012' effective_date, '05-AUG-2012' from dual
union all
select '100' grp, '01-JUL-2012' effective_date, '05-AUG-2012' from dual--Invalid data (New Condition)
union all
SELECT '100' GRP, '01-AUG-2012' EFFECTIVE_DATE, '15-AUG-2012' FROM DUAL-- Invalid record
union all
select '100' grp, '17-AUG-2012' effective_date, '31-AUG-2012' from dual-- Invalid record
union all
select '200' grp, '01-JUL-2012' effective_date, '31-JUL-2012' from dual
union all
select '200' grp, '28-JUL-2012' effective_date, '05-AUG-2012' from dual--Invalid record
),
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
minus
select grp, effective_date, termination_date
from correct_data
connect by prior termination_date = effective_date-1
and prior grp = grp
start with (grp, effective_date)
in (select grp, min(effective_date) from correct_data group by grp)
order by 1, 2
Expected output
100 02-JAN-12 29-JUN-12
100 01-JUL-12 05-AUG-12 (Query is not showing this record because its effective date is same like previous)
100 01-AUG-12 15-AUG-12
100 17-AUG-12 31-AUG-12
200 28-JUL-12 05-AUG-12
I'm very sorry because I cannot put all my test case together. In addition, If I will put date something like termination date < effective date in same row and then run query then I'm getting error . Thanks
Regards
Shumail
|
|
|
|
|
|
|
|
| Re: Need all invalid records [message #587169 is a reply to message #587159] |
Wed, 12 June 2013 20:03 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Keep in mind (and I already noted it in my reply to same question you posted on OTN), in general your task has no solution. No matter what solution you come up with, it has to determine first row and start chaining from it. Issue is how to decide which row is valid if table has multiple rows with min effective date. For example:
WITH
DATA AS
(
SELECT '100' GRP, '01-JAN-2012' EFFECTIVE_DATE, '30-JUN-2012' TERMINATION_DATE from DUAL
union all
SELECT '100' GRP, '02-JAN-2012' EFFECTIVE_DATE, '29-JUN-2012' FROM DUAL
union all
select '100' grp, '01-JUL-2012' effective_date, '05-AUG-2012' from dual
union all
SELECT '100' GRP, '01-AUG-2012' EFFECTIVE_DATE, '15-AUG-2012' FROM DUAL
union all
select '100' grp, '17-AUG-2012' effective_date, '31-AUG-2012' from dual
union all
select '200' grp, '01-JUL-2012' effective_date, '31-JUL-2012' from dual
union all
select '200' grp, '28-JUL-2012' effective_date, '05-AUG-2012' from dual
union all
select '100' grp, '01-JAN-2012' effective_date, '16-AUG-2012' from dual -- additional row
),
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
minus
select grp, effective_date, termination_date
from correct_data
connect by prior termination_date = effective_date-1
and prior grp = grp
start with (grp, effective_date)
in (select grp, min(effective_date) from correct_data group by grp)
order by 1, 2
/
GRP EFFECTIVE TERMINATI
--- --------- ---------
100 02-JAN-12 29-JUN-12
100 01-AUG-12 15-AUG-12
200 28-JUL-12 05-AUG-12
SQL>
As you can see, solution counted both rows in GRP=100 with effective date of Jan 1, 2012 as valid even though they overlap. So we need an additional rule to decide which one to consider valid. For example, based on min effective date and min termination date. Also, in real life DATA will be a real table and it is easier to provide solution for that. Therefore, I created table DATA:
SQL> SELECT *
2 FROM DATA
3 /
GRP EFFECTIVE_D TERMINATION
--- ----------- -----------
100 01-JAN-2012 30-JUN-2012
100 02-JAN-2012 29-JUN-2012
100 01-JUL-2012 05-AUG-2012
100 01-AUG-2012 15-AUG-2012
100 17-AUG-2012 31-AUG-2012
200 01-JUL-2012 31-JUL-2012
200 28-JUL-2012 05-AUG-2012
100 01-JAN-2012 16-AUG-2012
8 rows selected.
SQL>
And solution:
WITH VALID AS (
SELECT ROWID RID
FROM DATA D
START WITH (GRP,ROWID) IN (
SELECT GRP,
MIN(ROWID) KEEP(DENSE_RANK FIRST ORDER BY EFFECTIVE_DATE,TERMINATION_DATE)
FROM DATA
GROUP BY GRP
)
CONNECT BY GRP = PRIOR GRP
AND EFFECTIVE_DATE = PRIOR TERMINATION_DATE + 1
)
SELECT D.*,
NVL2(RID,'VALID','INVALID') STATUS
FROM DATA D
LEFT JOIN
VALID V
ON D.ROWID = V.RID
ORDER BY GRP,
EFFECTIVE_DATE
/
GRP EFFECTIVE_D TERMINATION STATUS
--- ----------- ----------- -------
100 01-JAN-2012 30-JUN-2012 VALID
100 01-JAN-2012 16-AUG-2012 INVALID
100 02-JAN-2012 29-JUN-2012 INVALID
100 01-JUL-2012 05-AUG-2012 VALID
100 01-AUG-2012 15-AUG-2012 INVALID
100 17-AUG-2012 31-AUG-2012 INVALID
200 01-JUL-2012 31-JUL-2012 VALID
200 28-JUL-2012 05-AUG-2012 INVALID
8 rows selected.
SQL>
SY.
|
|
|
|
| Re: Need all invalid records [message #587175 is a reply to message #587159] |
Wed, 12 June 2013 23:48 |
|
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
shumail wrote on Wed, 12 June 2013 22:12I'm sorry. I'm not getting you. Really appreciate if you can give me an example regarding your mentioned reply.
What I mean is (before your next example that shows a new case):
SQL> WITH
2 DATA AS
3 (
4 SELECT '100' GRP, '01-JAN-2012' EFFECTIVE_DATE, '30-JUN-2012' TERMINATION_DATE from DUAL
5 union all
6 SELECT '100' GRP, '02-JAN-2012' EFFECTIVE_DATE, '29-JUN-2012' FROM DUAL -- Invalid record
7 union all
8 select '100' grp, '01-JUL-2012' effective_date, '05-AUG-2012' from dual
9 union all
10 select '100' grp, '01-JUL-2012' effective_date, '05-AUG-2012' from dual--Invalid data (New Condition)
11 union all
12 SELECT '100' GRP, '01-AUG-2012' EFFECTIVE_DATE, '15-AUG-2012' FROM DUAL-- Invalid record
13 union all
14 select '100' grp, '17-AUG-2012' effective_date, '31-AUG-2012' from dual-- Invalid record
15 union all
16 select '200' grp, '01-JUL-2012' effective_date, '31-JUL-2012' from dual
17 union all
18 select '200' grp, '28-JUL-2012' effective_date, '05-AUG-2012' from dual--Invalid record
19 ),
20 correct_data as (
21 select grp,
22 to_date(effective_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH') effective_date,
23 to_date(termination_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH') termination_date
24 from data
25 )
26 select grp, effective_date, termination_date
27 from correct_data
28 minus
29 select grp, effective_date, termination_date
30 from correct_data
31 connect by prior termination_date = effective_date-1
32 and prior grp = grp
33 start with (grp, effective_date)
34 in (select grp, min(effective_date) from correct_data group by grp)
35 union all
36 select grp, effective_date, termination_date
37 from correct_data
38 group by grp, effective_date, termination_date
39 having count(*) = 2
40 order by 1, 2
41 /
GRP EFFECTIVE_DATE TERMINATION_DATE
--- ------------------- -------------------
100 02/01/2012 00:00:00 29/06/2012 00:00:00
100 01/07/2012 00:00:00 05/08/2012 00:00:00
100 01/08/2012 00:00:00 15/08/2012 00:00:00
100 17/08/2012 00:00:00 31/08/2012 00:00:00
200 28/07/2012 00:00:00 05/08/2012 00:00:00
5 rows selected.
Regards
Michel
|
|
|
|
|
|
|
|
| Re: Need all invalid records [message #587331 is a reply to message #587280] |
Fri, 14 June 2013 02:53 |
|
shumail
Messages: 149
Registered: September 2012
Location: Canada
|
Senior Member |
|
|
Hi All
I'm trying to learn the above mentioned query so I break up the query into small parts but I'm still not getting the connect by clause in this query. My query is something like that:
Query
WITH
DATA AS
(
select '100' grp, '01-JAN-2012' effective_date, '30-JUN-2012' termination_date from dual
union all
select '100' grp, '01-JUL-2012' effective_date, '05-AUG-2012' from dual
union all
select '100' grp, '06-AUG-2012' effective_date, '10-AUG-2012' from dual
union all
select '200' grp, '01-JUL-2012' effective_date, '31-JUL-2012' from dual
),
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,level
from correct_data
connect by prior termination_date = effective_date-1
and prior grp = grp
start with (grp, effective_date)
in (select grp, min(effective_date) from correct_data group by grp)
order by 1, 2;
It show the following result:
Result
GRP EFFECTIVE_DATE TERMINATION_DATE LEVEL
100 01-JAN-12 30-JUN-12 1
100 01-JUL-12 05-AUG-12 2
100 06-AUG-12 10-AUG-12 3
200 01-JUL-12 31-JUL-12 1
I'm not getting connect by part of the query because connect by clause will never become true but still the query return record and change level . Please help me out to learn this query. Thanks
Regards
Shumail
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Aug 08 03:47:49 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
