Finding a set [message #428778] |
Fri, 30 October 2009 00:20  |
jamieverg
Messages: 17 Registered: October 2009
|
Junior Member |
|
|
Hi All,
This is a one time script.
There are some 20 million history records. I need to identify unwanted records from a set and update one needed record and delete the work.
At the moment, I need to identify records that are part of a set so that I can delete them and update one of them
A set of records is identified by:
1. A unique set of PName, Area, GSTnumber, GSTid and Bpnumber
2. The start Date and End Date
For eg:
If the date are consecutive or they have the same dates ie, then they are part of the same set
16/07/2008 23:10 17/07/2008 18:36
17/07/2008 18:36 18/07/2008 21:34
17/07/2008 5:34 17/07/2008 19:12
21/07/2008 19:12 22/07/2008 18:50
If the dates are overlapping , then they are part of the same set.
16/07/2008 23:10 17/07/2008 18:36
17/07/2008 18:36 18/07/2008 21:34
18/07/2008 21:34 21/07/2008 19:12
21/07/2008 19:12 22/07/2008 18:50
18/07/2008 23:10 24/07/2008 18:36
If there is a gap , then they are not part of the same set even though the other colums are unique.
Eg:
16/07/2008 23:10 17/07/2008 18:36
17/07/2008 18:36 18/07/2008 21:34
18/07/2008 21:34 21/07/2008 19:12
21/07/2008 19:12 22/07/2008 18:50
18/07/2008 23:10 24/07/2008 18:36
29/07/2008 23:10 1/082008 18:36
1/08/2008 18:36 2/08/2008 21:34
Please look at my attachment for more info.
An early reply will be appreciated.
Thanks in advance.
-
Attachment: Excel2.csv
(Size: 4.75KB, Downloaded 852 times)
|
|
|
Re: Finding a set [message #428795 is a reply to message #428778] |
Fri, 30 October 2009 01:59  |
 |
Michel Cadot
Messages: 68734 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Post a working Test case: create table and insert statements along with the result you want with these data and explain it.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.
Regards
Michel
|
|
|