开发者

Matching records on multiple combinations of fields

开发者 https://www.devze.com 2023-03-25 06:50 出处:网络
I have a data table as follows FNameSNameDOBPCodeAddr1 ======== ====== =======开发者_运维技巧=====

I have a data table as follows

FName  SName  DOB  PCode   Addr1     
====   ==== ====== =====   ==开发者_运维技巧=====  
Bob    Test 231280 S66TH   1 Test Lane  
Bobby  Test 240977 S66TH   2 Test Drive
Robert Test 240977 xxxxx   xxxxxx
Tim    Test 140977 S78YH   5 Hill Road

I am trying to write a function / SP that will accept Paramters FNAme, SName, Dob, PCode and Addr1. I want to be able to match records where ANY 3 out of the 5 parameters are equal. I'm not sure how to do this WITHOUT writing a long series of AND OR statements? I would quite like the solution to be scalable too so if in future another column is added then 3 out of 6 must match (or even 4 out of 6). I dont mind the query being represented by Linq either.

Essentially Im looking for a matching algorithm That can match x out of y pieces of data.


Convert true comparisons to 1, false comparisons to 0, add them all together, and test if the sum is at least 3. Something like

where (case FName when @FName then 1 else 0 end)
    + (case SName when @Sname then 1 else 0 end)
    + (case DOB   when @DOB   then 1 else 0 end)
    + (case PCode when @PCode then 1 else 0 end)
    + (case Addr1 when @Addr1 then 1 else 0 end)
    >= 3
0

精彩评论

暂无评论...
验证码 换一张
取 消