Thread: Any SQL Gurus?

I have a problem im trying to solve but its just not hitting me. I have a table, lets says its defined like this.

Code:

SOMETABLE
    - OrderNumber
    - CustomerID
    - CreatedDate DATETIME

I am trying to write a query that will find me all duplicate records for a certain customerid.

So example data

Code:

10100, 58640, 2010-10-26 11:22:27.697
10200, 58640, 2010-10-26 14:82:27.697
10300, 58640, 2010-10-26 08:42:27.697
10400, 58640, 2010-10-28 14:32:27.697
10500, 58640, 2010-10-26 12:22:27.697

So the query would return 10100, 10200, 10300, 10500

The date compare has to be independent of time.

I just have hit a road block and I know it cant be this hard.

Anyone have any tips?

OK here it is


select a.ordernumber from orders a
where a.customerid in
(select b.customerid from
orders b
where b.orderdate > '12-dec-2010' and b.orderdate< '10-jan-2011'
group by b.customerid
having count ( b.customerid) > 1)

Let me know if you have any problems
Jaishree

Wow, your rock. No clue what I couldnt get that to work! Yours, with a tweak, worked great!

Thanks soo much!