May 17, 2012

Compare Values in the Same Column in sql

Need to locate customers who has placed their orders twice in a certain period. Now, the order might not be exactly the same, thus  cant use a normal query finding duplications, but rather compare the order values of a customer and indicate the orders whose values differ with about 20%..

the simplest would be

 select t1.customerid,t1.orderid,t1.ordervalue ,t2.ordervalue  
 from Table1 as T1  
 join Table1 as T2  
 on t1.orderid <> t1.orderid   
 and t1.customerid = t2.customerid  
 and t1.ordervalue < t2.ordervalue * 1.2  
 and t1.ordervalue > t2.ordervalue * .8