Solved

How to remove the overlapping data?

Posted on 2009-03-30
37
1,084 Views
Last Modified: 2013-12-19
Hi,
      I have data like below.
MODEL           PRICE            START_DATE_ACTIVE            END_DATE_ACTIVE
SONY             100                         1/1/1901                           12/31/2005
SONY             200                         1/1/2006                           12/31/2007
SONY             300                         1/1/2008                           12/31/2008
SONY             400                         2/1/2008                           12/31/2009
SONY             500                         1/1/2009                           12/31/2010
                     The above the model SONY is having the price range that depends on the start_date_active and end_date_active column. For example in the first row, the model SONY price is 100 from 1/1/2006 to 12/31/2007. The same model SONY price is 200 from 1/1/2008 to 12/31/2008. So the model price will come like this. But in some cases the date will be overlapping. See the row number 3 and 4. I should remove the rownumber 4. How can i remove the overlapping date data?

Thanks
0
Comment
  • 20
  • 12
  • 5
37 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 24024547
try this....
DELETE FROM   your_table

      WHERE   ROWID IN

                      (SELECT   rid

                         FROM   (SELECT   ROWID rid,

                                          start_date_active sda,

                                          end_date_active eda,

                                          LAG(end_date_active)

                                              OVER (

                                                  PARTITION BY model

                                                  ORDER BY start_date_active, end_date_active

                                              )

                                              prevend,

                                          LEAD(start_date_active)

                                              OVER (

                                                  PARTITION BY model

                                                  ORDER BY start_date_active, end_date_active

                                              )

                                              nextstart

                                   FROM   your_table)

                        WHERE   sda < prevend AND eda > nextstart)

Open in new window

0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 24026180
Hi thanks for your reply. It is returning no rows.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24026194

Can you clarify:
Row number 3 and 4 are overlapping but rownumber 4 exceeds the end dat of rownumber 3. Also rownumber 4 and 5 are overlapping but rownumber 5 end date exceeds the end date of row number4.
What is your ouptut if you don't have rownumber 5 in your sample set?
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 24027551
Hi sdstuber, i added one more condition in this query. It is not fetching any rows. In my table, i have 10000 rows. I want to see only the particular item. So i have added one AND condition in that query like below. Please verify my below query is correct or not.

SELECT * FROM   sample_table
      WHERE   ROWID IN
                      (SELECT   rid
                         FROM   (SELECT   ROWID rid,
                                          new_start_date sda,
                                          new_end_date eda,
                                          LAG(new_end_date)
                                              OVER (
                                                  PARTITION BY ITEM_NAME
                                                  ORDER BY new_start_date, new_end_date
                                              )
                                              prevend,
                                          LEAD(new_start_date)
                                              OVER (
                                                  PARTITION BY ITEM_NAME
                                                  ORDER BY new_start_date, new_end_date
                                              )
                                              nextstart
                                   FROM   sample_table)
                        WHERE   sda < prevend AND eda > nextstart)                                                
                        AND ITEM_NAME = 'PHILIPS'
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24027585
that will work or you can put it in the inner query
SELECT   *

  FROM   sample_table

 WHERE   ROWID IN

                 (SELECT   rid

                    FROM   (SELECT   ROWID rid,

                                     new_start_date sda,

                                     new_end_date eda,

                                     LAG(new_end_date)

                                         OVER (

                                             PARTITION BY item_name

                                             ORDER BY new_start_date, new_end_date

                                         )

                                         prevend,

                                     LEAD(new_start_date)

                                         OVER (

                                             PARTITION BY item_name

                                             ORDER BY new_start_date, new_end_date

                                         )

                                         nextstart

                              FROM   sample_table

                             WHERE   item_name = 'PHILIPS')

                   WHERE   sda < prevend AND eda > nextstart)

Open in new window

0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 24027589
Hi Sharath,
      you are correct. In my case overlapping means, it should not start with same year again with another row. For the above scenario, rownum 3 is ended with 12/31/2008. So that particular model price is deactivated after this date. So it has to start the new price from  1/1/2009  date. So nownum 5 is valid. In between rownum 3 and 5, again the start date started from the year which is already mentioned in rownum 3. So we should not consider this right. I thing now you can understand right.

Thanks
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 24030827
Hi sdstuber,
         If i change the condition like below, it is giving the overlapping data for me. See my attached file for my actual data.
WHERE   sda < prevend AND eda < nextstart). Can you tell me what is the reason?
TESTING-DATA.xls
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24031106
the problem was your original sample violated both ends
so,  sda < prevend AND eda > nextstart  found it.

but your new sample only violates on one end.
since all "good data will have eda < nextstart  that's not really a helpful condition.
so  sda < prevend AND eda < nextstart
would fail to find bad data like your original sample


0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24031149
changing the AND to OR will work except then you'll get 2 rows returns for each overlap
because a violation on either end will guarantee the other row will fail on the other end.

from your last example

2008/12/31 (end) on line 6 > 2008/12/01 (start) on line 7 so that's an overlap
but by the same logic  
2008/12/01 (start) on line 7 < 2008/12/31 (end) on line 6 so that's an overlap
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24031189
we could check on just one end, but then if you have cases like in your original example then you'll get false positives around the problem row.

To illustrate, from your first example, the 400 line is the problem.
if we check only the end then the 300 line will show up as a problem as well as the 400 line.
if we check only the start then the 500 line will show up as a problem as well as the 400 line

SONY             300                         1/1/2008                           12/31/2008
SONY             400                         2/1/2008                           12/31/2009
SONY             500                         1/1/2009                           12/31/2010
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 24031332
I don't want to remove 2 overlapping records. I want remove only the line number 7. becasue that start date is not ended to other rows. For example, if the end_date is 2008/12/31, the next row should start from 1/1/2009. But line number 6 is starting with 2008/12/01 right. So we need to consider this as a overlap. So i need to remove only those rows. But my first example also applicable for some cases. It should ended with after the end date of previous row. We need to consider both the cases.
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 24031345
Unless i can give only like below right.

WHERE   sda < prevend
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24031351
easiest solution is probably to run two deletes...

first delete all rows that have a double violation
then delete rows where the new_start_date < previous row's end date
DELETE FROM sample_table

      WHERE ROWID IN

                    (SELECT rid

                       FROM (SELECT ROWID rid,

                                    new_start_date,

                                    new_end_date,

                                    LAG(new_start_date)

                                        OVER (

                                            PARTITION BY item_name

                                            ORDER BY new_start_date, new_end_date

                                        )

                                        prevstart,

                                    LAG(new_end_date)

                                        OVER (

                                            PARTITION BY item_name

                                            ORDER BY new_start_date, new_end_date

                                        )

                                        prevend,

                                    LEAD(new_start_date)

                                        OVER (

                                            PARTITION BY item_name

                                            ORDER BY new_start_date, new_end_date

                                        )

                                        nextstart,

                                    LEAD(new_end_date)

                                        OVER (

                                            PARTITION BY item_name

                                            ORDER BY new_start_date, new_end_date

                                        )

                                        nextend

                               FROM sample_table

                              WHERE item_name = 'SONY')

                      WHERE new_start_date < prevend AND new_end_date > nextstart);
 

DELETE FROM sample_table

      WHERE ROWID IN

                    (SELECT rid

                       FROM (SELECT ROWID rid,

                                    new_start_date,

                                    new_end_date,

                                    LAG(new_start_date)

                                        OVER (

                                            PARTITION BY item_name

                                            ORDER BY new_start_date, new_end_date

                                        )

                                        prevstart,

                                    LAG(new_end_date)

                                        OVER (

                                            PARTITION BY item_name

                                            ORDER BY new_start_date, new_end_date

                                        )

                                        prevend,

                                    LEAD(new_start_date)

                                        OVER (

                                            PARTITION BY item_name

                                            ORDER BY new_start_date, new_end_date

                                        )

                                        nextstart,

                                    LEAD(new_end_date)

                                        OVER (

                                            PARTITION BY item_name

                                            ORDER BY new_start_date, new_end_date

                                        )

                                        nextend

                               FROM sample_table

                              WHERE item_name = 'SONY')

                      WHERE new_start_date < prevend );

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24031378
you can't "just" do the delete where next_start_date < prevend  because rows with double violation will cause a double row delete.  themselves plus the following row.

that's why I suggest doing it in two steps.
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 24031543
Ya it was great. I am putting those two queries in union and it is working for both condition. But if i make it as UNION, how should i delete?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24031581
don't combine them.

do two deletes

if you do both at once then you'll get extra deletes like I described above.

first delete the rows that violate on both ends
then delete the rows that violate on one end.
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 24031591
I mean how should i delete those records from the table?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24031613
if you want to try to combine them then you'll have to write a much more complicated set of filters in order to determine if rows that violate on only one end should be kept anyway because their violation is caused by another row's double ended violation.

much simpler to just do two deletes
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 24031623
oh sorry, didn't see you posted again.

how to delete?
with the two deletes I posted above.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24034205
can you try this?
select t1.MODEL,t1.PRICE,t1.START_DATE_ACTIVE,t1.END_DATE_ACTIVE

  from (select *,ROW_NUMBER() over (partition by Model order by Price) RN from your_table) t1

  left join (select *,ROW_NUMBER() over (partition by Model order by Price) RN from your_table) t2

    on t1.Model = t2.Model and t1.RN - 1 = t2.RN

  left join (select *,ROW_NUMBER() over (partition by Model order by Price) RN from your_table ) t3

    on t1.Model = t3.Model and t1.RN + 1 = t3.RN

 where case when (t1.START_DATE_ACTIVE < t2.END_DATE_ACTIVE) and 

                 (t1.END_DATE_ACTIVE > t3.START_DATE_ACTIVE) and 

                 (t1.END_DATE_ACTIVE < t3.END_DATE_ACTIVE)  then 1 else 0 end = 0

 order by t1.Model,t1.RN

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24034314
why query the table 3 times?

I don't understand the strategy behind the query,
I do understand the functionality of it, just not the why.


but, speaking of "why",  in my previously posted deletes, I included a couple of extra analytics that aren't really necessary.
I was experimenting and didn't take them out before posting.  They aren't harmful but aren't helpful either.

here are cleaner versions.  I'll admit, still "bigger" code than the previous post, but it should be much more efficient.
DELETE FROM sample_table

      WHERE ROWID IN

                    (SELECT rid

                       FROM (SELECT ROWID rid,

                                    new_start_date,

                                    new_end_date,

                                    LAG(new_end_date)

                                        OVER (

                                            PARTITION BY item_name

                                            ORDER BY new_start_date, new_end_date

                                        )

                                        prevend,

                                    LEAD(new_start_date)

                                        OVER (

                                            PARTITION BY item_name

                                            ORDER BY new_start_date, new_end_date

                                        )

                                        nextstart

                               FROM sample_table

                              WHERE item_name = 'SONY')

                      WHERE new_start_date < prevend AND new_end_date > nextstart);

 

DELETE FROM sample_table

      WHERE ROWID IN

                    (SELECT rid

                       FROM (SELECT ROWID rid,

                                    new_start_date,

                                    new_end_date,                                   

                                    LAG(new_end_date)

                                        OVER (

                                            PARTITION BY item_name

                                            ORDER BY new_start_date, new_end_date

                                        )

                                        prevend

                               FROM sample_table

                              WHERE item_name = 'SONY')

                      WHERE new_start_date < prevend );

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 24034548
<<I don't understand the strategy behind the query,
I do understand the functionality of it, just not the why. >>
Do you want explanation?


select t1.MODEL,t1.PRICE,t1.START_DATE_ACTIVE,t1.END_DATE_ACTIVE

  from (select *,ROW_NUMBER() over (partition by Model order by Price) RN from your_table) t1

  left join (select *,ROW_NUMBER() over (partition by Model order by Price) RN from your_table) t2

    on t1.Model = t2.Model and t1.RN - 1 = t2.RN

  left join (select *,ROW_NUMBER() over (partition by Model order by Price) RN from your_table ) t3

    on t1.Model = t3.Model and t1.RN + 1 = t3.RN

 where case when (t1.START_DATE_ACTIVE < t2.END_DATE_ACTIVE) and 

                 (t1.END_DATE_ACTIVE > t3.START_DATE_ACTIVE) and 

                 (t1.END_DATE_ACTIVE < t3.END_DATE_ACTIVE)  then 1 else 0 end = 0

 order by t1.Model,t1.RN

/* query result

MODEL	PRICE	START_DATE_ACTIVE	END_DATE_ACTIVE

SONY	100	1901-01-01 00:00:00.000	2005-12-31 00:00:00.000

SONY	200	2006-01-01 00:00:00.000	2007-12-31 00:00:00.000

SONY	300	2008-01-01 00:00:00.000	2008-12-31 00:00:00.000

SONY	500	2009-01-01 00:00:00.000	2010-12-31 00:00:00.000

*/

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24035030
not an explanation of the functionality (results) no.  I understand what it does and how it works.

but what are you trying to do?  it clearly produces different results than what the asker is looking for.
I assume you are doing something different intentionally, so what is it/why that approach?

Particularly since it's so "heavy" with 3 outer joins.

So, I know what it does, and even if I didn't, I ran it myself (after fixing small syntax errors) and saw the results.
I'm just not sure what direction you're going.

It might be a good approach but "as is" I don't know how to use what you've posted.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24035374
>> it clearly produces different results than what the asker is looking for.

Is it? Can you provide some example set where you are getting different results.
Asker - can you test this on your whole sample data and let me know where its failing. I don' say that its the perfect solution, i just tested this with the sample set provided but check against your complete table.
>> Particularly since it's so "heavy" with 3 outer joins.

JOINing 3 times may be over head, but to achieve the required output, i used this approach.
>> I assume you are doing something different intentionally, so what is it/why that approach?

I don't understand your meaning of "intentionally" here. I am trying to achieve the asker's expected result set.  
To remove the overlapping records, i am checking previous record and next records Start /End dates with current record's Start/End dates. (The condition in my CASE statement).
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24035445
Try this...

I've modified your query to fit the most recent structure posted by the asker
I populated it with the data from the spreadsheet, but changed the first row to not go back to 1901 then added more historical data


the goal is to delete the rows with overlaps
with the sample data below there are 2 overlaps
the row with a price of 400 and the second row with a price of 17500

If you run the 2 deletes I posted earlier you'll be left with 10 non-overlapping rows.

Your query doesn't return the 2 rows that should be deleted

I tried looking at in reverse, maybe you were trying to return the data that would be left after deletes; but it doesn't do that either.  

That's what I meant by "doing something intentionally".  It didn't appear to be a small mistake pursuing the same goal I had ben and simply got some unexpected results.  Your approach appeared so radically different from where the thread had been going that it seemed like you must be doing something different and on purpose and I simply don't understand.


drop table sample_table;
 

CREATE TABLE sample_table

AS

    SELECT price_list_name,

           item_name,

           price,

           TO_DATE(nsd, 'yyyy/mm/dd') new_start_date,

           TO_DATE(ned, 'yyyy/mm/dd') new_end_date

      FROM (SELECT 'Global Price List All' price_list_name,

                   'SONY' item_name,

                   15500 price,

                   '2005/01/01' nsd,

                   '2005/05/14' ned

              FROM DUAL

            UNION ALL

            SELECT 'Global Price List All', 'SONY', 16000, '2005/05/15', '2006/02/04' FROM DUAL

            UNION ALL

            SELECT 'Global Price List All', 'SONY', 16800, '2006/02/05', '2007/06/09' FROM DUAL

            UNION ALL

            SELECT 'Global Price List All', 'SONY', 17000, '2007/06/10', '2007/12/31' FROM DUAL

            UNION ALL

            SELECT 'Global Price List All', 'SONY', 17500, '2008/01/01', '2008/12/31' FROM DUAL

            UNION ALL

            SELECT 'Global Price List All', 'SONY', 17500, '2008/12/01', '2008/12/31' FROM DUAL

            UNION ALL

            SELECT 'Global Price List All', 'SONY', 18000, '2009/01/01', '2010/12/31' FROM DUAL

            union all

            SELECT 'Global Price List All', 'SONY', 100, '1901/01/01', '1995/12/31' FROM DUAL

            UNION ALL

            SELECT 'Global Price List All', 'SONY', 200, '1996/01/01', '1997/12/31' FROM DUAL

            UNION ALL

            SELECT 'Global Price List All', 'SONY', 300, '1998/01/01', '1998/12/31' FROM DUAL

            UNION ALL

            SELECT 'Global Price List All', 'SONY', 400, '1998/02/01', '1999/12/31' FROM DUAL

            UNION ALL

            SELECT 'Global Price List All', 'SONY', 500, '1999/01/01', '2000/12/31' FROM DUAL);

            

select t1.item_name,t1.PRICE,t1.new_START_DATE,t1.new_END_DATE

  from (select sample_table.*,ROW_NUMBER() over (partition by item_name order by Price) RN from sample_table) t1

  left join (select sample_table.*,ROW_NUMBER() over (partition by item_name order by Price) RN from sample_table) t2

    on t1.item_name = t2.item_name and t1.RN - 1 = t2.RN

  left join (select sample_table.*,ROW_NUMBER() over (partition by item_name order by Price) RN from sample_table ) t3

    on t1.item_name = t3.item_name and t1.RN + 1 = t3.RN

 where case when (t1.new_START_DATE < t2.new_END_DATE) and 

                 (t1.new_END_DATE > t3.new_START_DATE) and 

                 (t1.new_END_DATE < t3.new_END_DATE)  then 1 else 0 end = 0

 order by t1.item_name,t1.RN            

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 24035549
I missed one more condition in my previous post.
yes, I am trying to return the data that would be left after deletes.

select t1.item_name,t1.PRICE,t1.new_START_DATE,t1.new_END_DATE

  from (select sample_table.*,ROW_NUMBER() over (partition by item_name order by Price) RN from sample_table) t1

  left join (select sample_table.*,ROW_NUMBER() over (partition by item_name order by Price) RN from sample_table) t2

    on t1.item_name = t2.item_name and t1.RN - 1 = t2.RN

  left join (select sample_table.*,ROW_NUMBER() over (partition by item_name order by Price) RN from sample_table ) t3

    on t1.item_name = t3.item_name and t1.RN + 1 = t3.RN

 where case when t1.new_START_DATE <= t2.new_END_DATE 

            and ((t1.new_END_DATE <= t2.new_END_DATE) or 

                (t1.new_END_DATE >= t2.new_END_DATE and t1.new_END_DATE >= t3.new_START_DATE)) then 1 else 0 end = 0

 order by t1.item_name,t1.RN 

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24035816
ok, maybe I'm misunderstanding the request

I thought the goal was to actually "do" the deletes of the invalid rows

Suriyaraj_Sudalaiappan, can you please clarify?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 24035849
If the goal is simply to return the good data
and not actually delete the bad stuff
then try this...
SELECT price_list_name, item_name, price, new_start_date, new_end_date

  FROM (SELECT price_list_name,

               item_name,

               price,

               new_start_date,

               new_end_date,

               LAG(new_end_date)

                   OVER (PARTITION BY item_name ORDER BY new_start_date, new_end_date)

                   prevend

          FROM (SELECT price_list_name,

                       item_name,

                       price,

                       new_start_date,

                       new_end_date,

                       LAG(new_end_date)

                           OVER (PARTITION BY item_name ORDER BY new_start_date, new_end_date)

                           prevend,

                       LEAD(new_start_date)

                           OVER (PARTITION BY item_name ORDER BY new_start_date, new_end_date)

                           nextstart

                  FROM sample_table

                 WHERE item_name = 'SONY')

         WHERE new_start_date > NVL(prevend, TO_DATE('1-1-1', 'yyyy-mm-dd'))

            OR new_end_date < NVL(nextstart, TO_DATE('9999-12-31', 'yyyy-mm-dd')))

 WHERE new_start_date > NVL(prevend, TO_DATE('1-1-1', 'yyyy-mm-dd'))

Open in new window

0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 24047088
Hi,
  Shall i use the query like below. In my table, i have 9000 rows. I did not give any where condition to select the particular model.

SELECT rid
FROM (SELECT ROWID rid,
            price_list_name,
            item_name,
            uom_code,
            new_start_date,
            new_end_date,
            formula_name,
            LAG(new_end_date)
                OVER (
                    PARTITION BY item_name
                    ORDER BY new_start_date, new_end_date
                )
                prevend,
            LEAD(new_start_date)
                OVER (
                    PARTITION BY item_name
                    ORDER BY new_start_date, new_end_date
                )
                nextstart
        FROM SAMPLE_TABLE)
        WHERE (new_start_date < prevend)
        OR (new_start_date < prevend AND new_end_date > nextstart);
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24048653
what do you want to do with that query?


as you can see from the threads above there is some confusion as to what you are really trying to accomplish.
I have two guesses as to your goal and I've posted answers to both of them.


if you want to do a delete of data from your table, then no
use the two deletes I posted before in 24034314, run them in that same order

if you want to show records that will be kept if you were to do a delete then use the query I posted in 24035849 (you could also use Sharath's but it's less efficient)


if neither of those goals is really what you're trying to do, please try explaining again.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24048660
you can remove the model or item_name condition, of course
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 24049022
essdbdu33 $cp l3109359.dbg $XXOM_TOP/log
essdbdu33 $ls -l $XXOM_TOP/log
total 800
-rw-r--r--   1 rhuggi   aosimp    401791 Apr  1 12:53 l3109359.dbg
essdbdu33 $>l3109359.dbg
bash: l3109359.dbg: Permission denied
essdbdu33 $pwd
/apps/betsyp1/betsyp1db/10.2.0/temp/betsyp1_essdbdu33

Hi,
Actually i want to update my custom table if any overlapping data are there.
        Actually my requirement is, we need to identify the overlapping data from the table and update the same. Here overlapping means, we have to consider only the start date and end date columns. We have 'n' number of items in my table and it contains different prices. Price is different for same item with different start and end date. That means, price is applicable for only with in that period. So we should be start from one date and end with other date. That date should not be repeated multiple times. For example my sample data, it start with 1/1/2000 and end with 12/31/2001. Then the next date should start with 1/1/2002. It should not start 1/2/2002 and more than that. because my data is like that. If any start like 1/2/2002 and more than that we should update that row in the table as overlapping.
      And i got two scenarios from my data sheet like below. The start date is same for two rows and end date is different. Some data start date is different and end date is same. I will give the example. In this we don't know which one we should remove. For that discussion is going on right now. I will let you know after they confirmed.

For first scenario:
START_DATE      END_DATE
10/28/2006      12/31/2006
10/28/2006      12/31/2010
For second scenario:
START_DATE      END_DATE
1/1/2008            12/31/2010
1/2/2008            12/31/2010

 Below query i modified from your query. Please verify that and let me know if any changes. And i included one more condition here like pps1.new_end_date <> prevend. It will helpful for my second scenario to remove same end date and consider only one. But not decided this one.
UPDATE /*+index(pps) */
                XXQP_PAM_PRICE_STG pps
            SET
                pps.process_flag = 3,
                pps.error_msg = 'Overlapping data, not consider for Import'
            WHERE
                pps.process_flag = 1
                AND pps.ROWID IN (
                            SELECT /*+index(pps1) */ rid
                            FROM (
                                SELECT ROWID rid,
                                        price_list_name,
                                        item_name,
                                        uom_code,
                                        new_start_date,
                                        new_end_date,
                                        formula_name,
                                        LAG(new_end_date)
                                            OVER (
                                                PARTITION BY item_name
                                                ORDER BY new_start_date, new_end_date
                                            )
                                            prevend,
                                        LEAD(new_start_date)
                                            OVER (
                                                PARTITION BY item_name
                                                ORDER BY new_start_date, new_end_date
                                            )
                                            nextstart
                                    FROM XXQP_PAM_PRICE_STG
                                    ) pps1
                            WHERE ((pps1.new_start_date < prevend AND pps1.new_end_date <> prevend) OR (pps1.new_start_date < prevend AND pps1.new_end_date > nextstart))
                            AND pps1.item_name = pps.item_name
                            );

Thanks      
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 24049034
don't consider from the staring in my previous comment. some copy pase mistake. Please read from "HI"
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24049321
so,  this entire question has been wrong then it seems.

you're not trying to do a delete at all
and, you're not just trying to find overlaps, but you're also trying to find holes?

your description of one record ending 12/31/2001 but the next record should not start 1/2/2002
is that because it would leave a "hole" of 1/1/2001?


please post complete example of data (not a query)
please use a create table like I posted above with your entire table structure and enough sample data to show all scenarios (good and bad) you want to consider.

then post what you want the table to look like afterward.  Don't worry about trying to guess what query might be used, just manually create the data in its final form and we'll try to figure out what query will produce the results you are looking for






0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24050832
by the way, please close your old questions, you have open questions, including apparent repeats (I just posted to one of them) over 2 months old.
0
 

Author Closing Comment

by:Suriyaraj_Sudalaiappan
ID: 31564275
GOOD ONE
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24775005
glad I could help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle 12c 10 100
Need help with Oracle syntax 4 42
Identify records which do NOT qualify for a view 9 30
sql query 9 22
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now