Hi Pavan,

what is the logic to be applied to generate the UID?

Hi Jacob,

i would like assign unique ID whom have  No_Match like 2,3 and so on... i have large dataset but this is just sample.

so ID will be generate by query for assigning ID?

is that thing you are asking?

Thanks in advance...

I asked this question because I saw that the UID column in your sample data is not unique. Records with year 1960 show "1" as UID. 1920 shows "2" and 1965 shows "3". What is the logic to be used to get these values?

sorry for confusion..not UID but who ever people match by first name ,last name and DOB ,same id will be assign through dataset..

i have used following logic to get NO_Match.

WITH

CTE AS (

*,COUNT (*) OVER(PARTITION BY FirstName, LastName, DOB) AS cnt

where Firstname is not null And lastname is not null and DateOFBirth is not null

)

*

FROM cte WHERE cnt >1

Thanks

How about this?

DECLARE @t TABLE (
    fname varchar(20), lname varchar(20), dob smalldatetime )
INSERT INTO @t (fname, lname, dob)
SELECT 'sudhir','shah','1/1/1960' UNION ALL
SELECT 'sudhir','shah','1/1/1960' UNION ALL
SELECT 'sandip','kumar','2/2/1920' UNION ALL
SELECT 'farhan','khan','3/3/1965' UNION ALL
SELECT 'sandip','kumar','2/2/1920' UNION ALL
SELECT 'farhan','khan','3/3/1965' UNION ALL
SELECT 'sandip','kumar','2/2/1920'

SELECT 
    lname, 
    fname,
    dob,
    DENSE_RANK() OVER(ORDER BY fname, lname, dob) AS UID
FROM @t
/*
lname  fname   dob         UID
------ ------- ----------- ---
khan   farhan  1965-03-03  1
khan   farhan  1965-03-03  1
kumar  sandip  1920-02-02  2
kumar  sandip  1920-02-02  2
kumar  sandip  1920-02-02  2
shah   sudhir  1960-01-01  3
shah   sudhir  1960-01-01  3
*/

Thanks jacob,

you read my mind...its work ..

Thank you very much!!!