SQLTeam.com | Weblogs | Forums

How to check duplicate in cross way?

sql2014
sql2008

#1

Hi Team,

Surnme|     forename|   work|  Creation date|  Active/inactive
------------------------------------------------------------------
satish| Amuluru     |   123 |   12/11/2016  |   D

Amuluru| satish     |  123  |   16/11/2016  |   A

satish| Amuluru     |  123  |  16/1/2016    |   D

satish| Amuluru     |  123  |  16/1/2016    |   D

mohamd| arashad     |   123 |               |   I

Here we want to compare here I have to find how many duplicate records are there and The crieteria for duplication is below.

1)cross checking means

(surname =forename or forename=surname) and work should same.

We update that Active/inactive column as "D" if that duplicate record does not contain latest creation date. If that record contain latest creation date then update Active/inactive column as "A".

  1. Same matching.

(surname=surname or forename=forename) and work should same.

We update that Active/inactive column as "D" if that duplicate record does not contain latest creation date. If that record contain latest creation date then update Active/inactive column as "A". And based on latest creation date on duplicate record we have to update Active/inactive column as "A" and which column don`t have duplicate record those will update Active/inactive column as "I".

Here I am uploading image please prefer that one in this image i explain problem may be get clear idea. Thanks .

Thanks.