Home » SQL & PL/SQL » SQL & PL/SQL » Finding a set (Oracle 9i , PL/SQL)
Finding a set [message #428778] Fri, 30 October 2009 00:20 Go to next message
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 Go to previous message
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

Previous Topic: Update Problem
Next Topic: ora-29283 invalid file operation
Goto Forum:
  


Current Time: Wed Feb 12 09:10:21 CST 2025