Re: Find Duplicate Values with Varying Start Dates

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 1 Mar 2006 00:27:00 +0100
Message-ID: <4404dc47$0$27603$636a55ce@news.free.fr>

"is_db" <is_db_at_hotmail.com> a écrit dans le message de news: 1141021106.995091.282030_at_i39g2000cwa.googlegroups.com...
|I am trying to find all duplicate values in a 7-day date range so that
| if an item/customer combination exists more than once either 3-days
| before or after the date being evaluated it will be returned as having
| a duplicate. All days in the table need to be evaluated in a loop
| fashion so that each item is compared to all duplicate item/customer
| combinations in the 3-day period before or after the sale date.
|
| For example, with:
|
| Column 1: Date
| Column 2: Item
| Column 3: Customer
|
| And values:
|
| 1/1/06
| Item A
| Customer A
|
| 1/2/06
| Item A
| Customer A
|
| 1/3/06
| Item B
| Customer A
|
| 1/4/06
| Item A
| Customer A
|
| When 1/2/06 is evaluated it would evaluate Item A/Customer A as a
| duplicate because an equivalent item was found on 1/1/06 and on 1/4/06.
| In this example, 1/1/06, 1/2/06, and 1/4/06 would all be returned as
| duplicates.
|

SQL> select * from t order by c1, c2, c3   2 /

C1                  C2                   C3
---------- -------------------- --------------------
01/01/2006 Item A               Customer A
02/01/2006 Item A               Customer A
03/01/2006 Item B               Customer A
04/01/2006 Item A               Customer A

4 rows selected.

SQL> with
  2 step1 as (

  3      select c1, c2, c3,
  4             count(*) over (partition by c2, c3
  5                            order by c1
  6                            range between interval '3' day preceding
  7                                      and interval '3' day following)
  8               cnt
  9      from t

 10 )
 11 select to_char(c1, 'MM/DD/YYYY') c1, c2, c3  12 from step1
 13 where cnt > 1
 14 order by c1, c2, c3
 15 /

C1         C2                   C3
---------- -------------------- --------------------
01/01/2006 Item A               Customer A
01/02/2006 Item A               Customer A
01/04/2006 Item A               Customer A

3 rows selected.

Regards
Michel Cadot Received on Tue Feb 28 2006 - 17:27:00 CST