March 11, 2010 at 3:21 am
I have a table, table contain 5 Column (SSN, DOB, Gender, FirstName, LastName)
I have match data based on SSN and any Three filed If SSN and Any three filed match then Return 1 else 0 .
I don’t want to put all permutation /Combination, Is any simple way do that in T-sql
March 11, 2010 at 5:14 am
One way to achieve it:
DECLARE @SampleData
TABLE (
SSN BIGINT NOT NULL PRIMARY KEY,
DOB DATETIME NOT NULL,
Gender CHAR(1) NOT NULL,
FirstName NVARCHAR(30) NOT NULL,
LastName NVARCHAR(30) NOT NULL
);
INSERT @SampleData
(SSN, DOB, Gender, FirstName, LastName)
VALUES (123456789, {d '1977-04-14'}, 'M', 'Bob', 'Smith');
DECLARE @SSN BIGINT,
@DOB DATETIME,
@Gender CHAR(1),
@FirstName NVARCHAR(30),
@LastName NVARCHAR(30);
SET @SSN = 123456789; -- Match (required)
SET @DOB = {d '1977-04-14'}; -- Match (1)
SET @Gender = 'M'; -- Match (2)
SET @FirstName = 'Robert'; -- No match
SET @LastName = 'Smith'; -- Match (3)
SELECT SD.SSN,
SD.DOB,
SD.Gender,
SD.FirstName,
SD.LastName
FROM @SampleData SD
WHERE SD.SSN = 123456789
AND 3 <=
CASE WHEN @DOB = SD.DOB THEN 1 ELSE 0 END +
CASE WHEN @Gender = SD.Gender THEN 1 ELSE 0 END +
CASE WHEN @FirstName = SD.FirstName THEN 1 ELSE 0 END +
CASE WHEN @LastName = SD.LastName THEN 1 ELSE 0 END;
Paul
March 11, 2010 at 5:24 am
Thank You , Greate Help
March 11, 2010 at 5:28 am
No worries.
You can help us to help you next time by including sample data in your question, just like I did in the answer.
Paul
March 11, 2010 at 6:19 am
I will do from next time . Thanks
March 11, 2010 at 6:27 am
I will check, you know...;-) 😛 :w00t:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply