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.

is_db wrote:
And what help would you like?

First write a SQL statement that returns the range you want. Then turn
it into an in-line view by wrapping parentheses around it and performing
a SELECT COUNT(*) with HAVING COUNT(*) > 1;

After that do whatever it is you intend to do with the offending set of
records.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

I apologize for any confusion my question my have caused.

I am hoping to get suggestions on how to develop a query to return all
duplicate records for an entire table--a duplicate row would have an
identical customer and item that was recorded 3-days before or 3-days
after the date of the row being evaluated. Since each record in a
1-year timeframe is being evaluated, I'm unsure how to specify the
date range (it changes relative to each row).

I appreciate any suggestions you may have.

is_db wrote:
What is wrong with the answers you've already received?
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

"is_db" <is_db (AT) hotmail (DOT) com> a écrit dans le message de news: 1141021106.995091.282030 (AT) i39g20...oglegroups.com...
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

Thank you very much. This worked perfectly.

I think I may have misunderstood what was said originally. I was able
to get this to work. Thank you for your help.