I have table like below.I wanted to get the duplicate records.Here the condition if date2 and date4 having same date OR dates within less than or equals to 10 days of each other then records are duplicate. I have around 2000 records in the DB.Showing here few sample example. Date1 can be ignored. It may be same date or may be different.
ID Number Code Type date1 Date2 date3 Date4 status shortname CP deferred
1 EO2 C TO 9/20/2000 9/1/2010 9/18/2010 9/1/2010 Archi Ordinary 58.65586 0
2 EO2 C TO 9/20/2000 9/5/2010 9/18/2010 9/5/2010 Archi Ordinary 58.65586 0
3 EO2 C TO 9/21/2000 9/10/2010 9/18/2010 9/10/2010 Archi Ordinary 58.65586 0
4 EO2 C TO 9/21/2000 9/24/2010 9/18/2010 9/24/2010 Archi Ordinary 58.65586 0
I have written below query:
select * from T a
join T b on a.ID = b.ID
where a.[Number] = b.[Number] and a.ID >1
Also, I tied this:
SELECT * FROM T a WHERE a.Id IN (SELECT b.Id FROM T b)
EXCEPT
SELECT * FROM T a
The problem is I'm not able to find a way, where each row can be compared with each other with above date condition.I should get the result like below as duplicate:
Number Code Type date1 Date2 date3 Date4 status shortname CP deferred
EO2 C TO 9/20/2000 9/1/2010 9/18/2010 9/1/2010 Archi Ordinary 58.65586 0
EO2 C TO 9/20/2000 9/5/2010 9/18/2010 9/5/2010 Archi Ordinary 58.65586 0
EO2 C TO 9/21/2000 9/10/2010 9/18/2010 9/10/2010 Archi Ordinary 58.65586 0
Please help.Thanks.
This is how I solved this. Thanks for help.