question

The ‘SSN matching’ SQL Problem

After we have fixed the performance problems with the codes written by Robert Bar and his brother Reggie Bar, now has the time come to their cousin, Fu Bar.

She was facing a not-so-simple problem by matching a list of user-entered SSN's (from a web page) with a list of validated SSN's. So far so good, right, but as we know, users aren't that particular careful when entering their personal information, so the SSN's have a number of errors in them.
The good thing is that all user-entered SSN's are checksum-validated and have duplicates removed.
All files (setup, sample data population and Fu Bar's code) can be downloaded from here. Fu's code is updated 10am Sunday 16th of May 2010 UTC. If you have downloaded the code before this time, please re-download.

The rules are as follow (in addition to the standard rules here)

1) A perfect match is valid
2) One single digit wrong is valid
3) Two digits wrong are valid, as long as they are not adjacent
4) Three or more digits wrong is invalid
5) If a user-entered SSN has a perfect match with a validated SSN, this user-entered SSN cannot be used in a combination with another validated SSN.
6) You are not allowed to change the schema of the two tables, with indexes as the only exception.

To clarify the non-adjacent rule:
a) A digit wrong in an odd position is allowed with a digit wrong on another odd position.
b) A digit wrong in an even position is allowed with a digit wrong on another even position.
c) A digit wrong in an odd position is not allowed with a digit wrong for any even position. This is because there is one flaw in the checksum algorithm used for the user-entered SSN's.
d) For very same reason as "c)", a digit wrong in an even position is not allowed with a digit wrong for any odd position.

Your task is to rewrite Fu's code (her code takes about 70-75 minutes to run) to produce a list in following style:

vSSN, ueSSN, Status

vSSN is the validated SSN from dbo.Validated table.
ueSSN is the user-entered SSN from the dbo.UserEntered table.
Status column is a tinyint describing how many characters that are wrong. 0 is a perfect match, 1 is one digit wrong match and 2 is a two digit wrong match.

The sorting is vital, because helpdesk will receive this output later for manual correction so an output with order by vSSN, ueSSN and Status is mandatory.

All entries have to be submitted before 9pm Sunday 30th of May 2010 (UTC). Entries will be evaluated as they are submitted and time allows. Final winner will be announced shortly after that.

First Prize for SQL Speed Phreak Challenge #5: \$100 and a license for SQL Data Generator (used when creating the sample data) !

There will also be three categorized runner-up prizes:

``````* Best Cursor-based solution
* Best CLR solution
* Best Unusual/experimental attempt
``````

The winners of these prizes will get a licensed copy of either SQL Prompt or SQL Refactor, whichever they prefer.

Don't forget to have fun and to be creative,

//Peso

Phil Factor:

The SSN problem was one of the most enjoyable to take part in, because the problem as so simple to define, but the answer was tricky to get right. In the end, all the solutions except for cursor-based ones, were performing in under a second, which made the final order rather more difficult to determine.

When differences are in tens of milliseconds, you can get a different order on different machines: This is a problem but when the size of the test data was determined, the existing cursor-based solution was running in fourteen minutes. We never predicted the extent of the Speed Phreakery we could achieve. The first set-based SQL solutions had this down to less than a handful of seconds, and soon the solutions were fighting in around the half-second mark.

The CLR-based solutions probably were at a slight advantage with this problem though it would be nice to see if this is true of different sizes of data. Matt and JAhlen were neck and neck with this, though JAhlen was a node ahead over the finishing line.

Daniel’s SSIS solution was a sober warning that one should never neglect the obvious answers. It gets the award for the Best Unusual/experimental attempt.

Matt was the clear winner of the best SQL solution (OK, Fu Bar was banned from the best Cursor-based solution). I hope he will want the license for Prompt/Refactor, (excellent for quickly formatting code) though he writes his own excellent SQL tools which are well worth a look.

Winner of the First Prize for the SQL Speed Phreak Challenge #5: \$100 and a license for SQL Data Generator (used when creating the sample data) was Johan Åhlén (JAhlen).

It was a very difficult decision as to whether Daniel or Johan won since it was a photofinish. We asked various experts, but nobody would venture an opinion. (The actual timings will vary considerably on different servers, be warned. The amount of memory, no. Of CPUs and general machine configuration will make a difference). I almost had to toss a coin!

Kathi Kellenberger, who wrote the excellent http://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/ Set-based Speed Phreakery: The FIFO Stock Inventory SQL Problem and http://www.simple-talk.com/sql/performance/writing-efficient-sql-set-based-speed-phreakery/ Writing Efficient SQL: Set-Based Speed Phreakery, is studying the results of this competition with the intention of writing it up for Simple-Talk like the others.

Many thanks to all. Peso for all the hard work, and for constructing such an interesting problem. Matt for his energy in coming up with both CLR and TSQL solutions, Scott for kicking things off, Fu Bar for trying her best, Daniel for thinking laterally, Johan for showing the way with the CLR routines, and all those whose comments encouraged the participants to greater efforts.

[1]: http://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Peso, My question is with #5. I wanted to confirm that you DO want to allow the inverse of this: basically if a user-entered SSN matches perfectly to a validated SSN this validated SSN can be used with another user-entered SSN. Eg select * from Validated v join UserEntered ue on ue.SSN = v.SSN where v.SSN='545013815' has a perfect match but is also returned by Fu for a user that has entered it incorrectly: 545515815 Thx, have a great weekend
0 Likes 0 ·
It's correct. The vSSN "545013815" has a perfect match with a user-entered SSN "545013815" and as such, the user-entered SSN "545013815" cannot be used for any other match. However, there can be more user-entered SSN's which can match the validated SSN "545013815", and it does with the user-entered SSN "545515815". Also make sure you have downloaded the latest code for Fu. I discovered she didn't comply to all the rules. Shame on her! :-) Fu's code was re-uploaded for an hour ago. //Peso
0 Likes 0 ·
Thanks Peso, I think that was it
0 Likes 0 ·
I didn't think Fu modified the script that much but it went from 7min to 45min
0 Likes 0 ·
That's the beauty of OR vs AND! :-)
0 Likes 0 ·

· Write an Answer

2010.5.16 Scot Hauder v1

I'm titling this one: "Compute Scalar" :)

```SELECT val.SSN [vSSN], uen.SSN [ueSSN],
CASE WHEN SUBSTRING(val.SSN,1,1) = SUBSTRING(uen.SSN,1,1) THEN 0 ELSE 1 END +
CASE WHEN SUBSTRING(val.SSN,2,1) = SUBSTRING(uen.SSN,2,1) THEN 0 ELSE 1 END +
CASE WHEN SUBSTRING(val.SSN,3,1) = SUBSTRING(uen.SSN,3,1) THEN 0 ELSE 1 END +
CASE WHEN SUBSTRING(val.SSN,4,1) = SUBSTRING(uen.SSN,4,1) THEN 0 ELSE 1 END +
CASE WHEN SUBSTRING(val.SSN,5,1) = SUBSTRING(uen.SSN,5,1) THEN 0 ELSE 1 END +
CASE WHEN SUBSTRING(val.SSN,6,1) = SUBSTRING(uen.SSN,6,1) THEN 0 ELSE 1 END +
CASE WHEN SUBSTRING(val.SSN,7,1) = SUBSTRING(uen.SSN,7,1) THEN 0 ELSE 1 END +
CASE WHEN SUBSTRING(val.SSN,8,1) = SUBSTRING(uen.SSN,8,1) THEN 0 ELSE 1 END +
CASE WHEN SUBSTRING(val.SSN,9,1) = SUBSTRING(uen.SSN,9,1) THEN 0 ELSE 1 END [Status]

FROM (
SELECT v.[SSN], x.SSN2
FROM [dbo].[Validated] v

CROSS APPLY (SELECT SUBSTRING(v.SSN,2,1) + SUBSTRING(v.SSN,4,1) + SPACE(2)
+ SUBSTRING(v.SSN,1,1)
+ SUBSTRING(v.SSN,3,1)
+ SUBSTRING(v.SSN,5,1)
+ SUBSTRING(v.SSN,7,1)
+ SUBSTRING(v.SSN,9,1) [SSN2] UNION ALL
SELECT SUBSTRING(v.SSN,2,1) + SPACE(1) + SUBSTRING(v.SSN,6,1) + SPACE(1)
+ SUBSTRING(v.SSN,1,1)
+ SUBSTRING(v.SSN,3,1)
+ SUBSTRING(v.SSN,5,1)
+ SUBSTRING(v.SSN,7,1)
+ SUBSTRING(v.SSN,9,1) UNION ALL
SELECT SUBSTRING(v.SSN,2,1) + SPACE(2) + SUBSTRING(v.SSN,8,1)
+ SUBSTRING(v.SSN,1,1)
+ SUBSTRING(v.SSN,3,1)
+ SUBSTRING(v.SSN,5,1)
+ SUBSTRING(v.SSN,7,1)
+ SUBSTRING(v.SSN,9,1) UNION ALL
SELECT SPACE(1) + SUBSTRING(v.SSN,4,1) + SUBSTRING(v.SSN,6,1) + SPACE(1)
+ SUBSTRING(v.SSN,1,1)
+ SUBSTRING(v.SSN,3,1)
+ SUBSTRING(v.SSN,5,1)
+ SUBSTRING(v.SSN,7,1)
+ SUBSTRING(v.SSN,9,1) UNION ALL
SELECT SPACE(1) + SUBSTRING(v.SSN,4,1) + SPACE(1) + SUBSTRING(v.SSN,8,1)
+ SUBSTRING(v.SSN,1,1)
+ SUBSTRING(v.SSN,3,1)
+ SUBSTRING(v.SSN,5,1)
+ SUBSTRING(v.SSN,7,1)
+ SUBSTRING(v.SSN,9,1) UNION ALL
SELECT SPACE(2) + SUBSTRING(v.SSN,6,1) + SUBSTRING(v.SSN,8,1)
+ SUBSTRING(v.SSN,1,1)
+ SUBSTRING(v.SSN,3,1)
+ SUBSTRING(v.SSN,5,1)
+ SUBSTRING(v.SSN,7,1)
+ SUBSTRING(v.SSN,9,1) UNION ALL

SELECT SUBSTRING(v.SSN,2,1)
+ SUBSTRING(v.SSN,4,1)
+ SUBSTRING(v.SSN,6,1)
+ SUBSTRING(v.SSN,8,1)
+ SPACE(2) + SUBSTRING(v.SSN,5,1) + SUBSTRING(v.SSN,7,1) + SUBSTRING(v.SSN,9,1) UNION ALL
SELECT SUBSTRING(v.SSN,2,1)
+ SUBSTRING(v.SSN,4,1)
+ SUBSTRING(v.SSN,6,1)
+ SUBSTRING(v.SSN,8,1)
+ SPACE(1) + SUBSTRING(v.SSN,3,1) + SPACE(1) + SUBSTRING(v.SSN,7,1) + SUBSTRING(v.SSN,9,1) UNION ALL
SELECT SUBSTRING(v.SSN,2,1)
+ SUBSTRING(v.SSN,4,1)
+ SUBSTRING(v.SSN,6,1)
+ SUBSTRING(v.SSN,8,1)
+ SPACE(1) + SUBSTRING(v.SSN,3,1) + SUBSTRING(v.SSN,5,1) + SPACE(1) + SUBSTRING(v.SSN,9,1) UNION ALL
SELECT SUBSTRING(v.SSN,2,1)
+ SUBSTRING(v.SSN,4,1)
+ SUBSTRING(v.SSN,6,1)
+ SUBSTRING(v.SSN,8,1)
+ SPACE(1) + SUBSTRING(v.SSN,3,1) + SUBSTRING(v.SSN,5,1) + SUBSTRING(v.SSN,7,1) + SPACE(1) UNION ALL
SELECT SUBSTRING(v.SSN,2,1)
+ SUBSTRING(v.SSN,4,1)
+ SUBSTRING(v.SSN,6,1)
+ SUBSTRING(v.SSN,8,1)
+ SUBSTRING(v.SSN,1,1) + SPACE(2) + SUBSTRING(v.SSN,7,1) + SUBSTRING(v.SSN,9,1) UNION ALL
SELECT SUBSTRING(v.SSN,2,1)
+ SUBSTRING(v.SSN,4,1)
+ SUBSTRING(v.SSN,6,1)
+ SUBSTRING(v.SSN,8,1)
+ SUBSTRING(v.SSN,1,1) + SPACE(1) + SUBSTRING(v.SSN,5,1) + SPACE(1) + SUBSTRING(v.SSN,9,1) UNION ALL
SELECT SUBSTRING(v.SSN,2,1)
+ SUBSTRING(v.SSN,4,1)
+ SUBSTRING(v.SSN,6,1)
+ SUBSTRING(v.SSN,8,1)
+ SUBSTRING(v.SSN,1,1) + SPACE(1) + SUBSTRING(v.SSN,5,1) + SUBSTRING(v.SSN,7,1) + SPACE(1) UNION ALL
SELECT SUBSTRING(v.SSN,2,1)
+ SUBSTRING(v.SSN,4,1)
+ SUBSTRING(v.SSN,6,1)
+ SUBSTRING(v.SSN,8,1)
+ SUBSTRING(v.SSN,1,1) + SUBSTRING(v.SSN,3,1) + SPACE(2) + SUBSTRING(v.SSN,9,1) UNION ALL
SELECT SUBSTRING(v.SSN,2,1)
+ SUBSTRING(v.SSN,4,1)
+ SUBSTRING(v.SSN,6,1)
+ SUBSTRING(v.SSN,8,1)
+ SUBSTRING(v.SSN,1,1) + SUBSTRING(v.SSN,3,1) + SPACE(1) + SUBSTRING(v.SSN,7,1) + SPACE(1) UNION ALL
SELECT SUBSTRING(v.SSN,2,1)
+ SUBSTRING(v.SSN,4,1)
+ SUBSTRING(v.SSN,6,1)
+ SUBSTRING(v.SSN,8,1)
+ SUBSTRING(v.SSN,1,1) + SUBSTRING(v.SSN,3,1) + SUBSTRING(v.SSN,5,1) + SPACE(2))x
)val
JOIN
(SELECT  ue.[SSN], y.SSN2
FROM [dbo].[UserEntered] ue
LEFT JOIN [dbo].[Validated] v ON (v.SSN = ue.SSN)
CROSS APPLY (SELECT SUBSTRING(ue.SSN,2,1) + SUBSTRING(ue.SSN,4,1) + SPACE(2)
+ SUBSTRING(ue.SSN,1,1)
+ SUBSTRING(ue.SSN,3,1)
+ SUBSTRING(ue.SSN,5,1)
+ SUBSTRING(ue.SSN,7,1)
+ SUBSTRING(ue.SSN,9,1) [SSN2] UNION ALL
SELECT SUBSTRING(ue.SSN,2,1) + SPACE(1) + SUBSTRING(ue.SSN,6,1) + SPACE(1)
+ SUBSTRING(ue.SSN,1,1)
+ SUBSTRING(ue.SSN,3,1)
+ SUBSTRING(ue.SSN,5,1)
+ SUBSTRING(ue.SSN,7,1)
+ SUBSTRING(ue.SSN,9,1) UNION ALL
SELECT SUBSTRING(ue.SSN,2,1) + SPACE(2) + SUBSTRING(ue.SSN,8,1)
+ SUBSTRING(ue.SSN,1,1)
+ SUBSTRING(ue.SSN,3,1)
+ SUBSTRING(ue.SSN,5,1)
+ SUBSTRING(ue.SSN,7,1)
+ SUBSTRING(ue.SSN,9,1) UNION ALL
SELECT SPACE(1) + SUBSTRING(ue.SSN,4,1) + SUBSTRING(ue.SSN,6,1) + SPACE(1)
+ SUBSTRING(ue.SSN,1,1)
+ SUBSTRING(ue.SSN,3,1)
+ SUBSTRING(ue.SSN,5,1)
+ SUBSTRING(ue.SSN,7,1)
+ SUBSTRING(ue.SSN,9,1) UNION ALL
SELECT SPACE(1) + SUBSTRING(ue.SSN,4,1) + SPACE(1) + SUBSTRING(ue.SSN,8,1)
+ SUBSTRING(ue.SSN,1,1)
+ SUBSTRING(ue.SSN,3,1)
+ SUBSTRING(ue.SSN,5,1)
+ SUBSTRING(ue.SSN,7,1)
+ SUBSTRING(ue.SSN,9,1) UNION ALL
SELECT SPACE(2) + SUBSTRING(ue.SSN,6,1) + SUBSTRING(ue.SSN,8,1)
+ SUBSTRING(ue.SSN,1,1)
+ SUBSTRING(ue.SSN,3,1)
+ SUBSTRING(ue.SSN,5,1)
+ SUBSTRING(ue.SSN,7,1)
+ SUBSTRING(ue.SSN,9,1) UNION ALL

SELECT SUBSTRING(ue.SSN,2,1)
+ SUBSTRING(ue.SSN,4,1)
+ SUBSTRING(ue.SSN,6,1)
+ SUBSTRING(ue.SSN,8,1)
+ SPACE(2) + SUBSTRING(ue.SSN,5,1) + SUBSTRING(ue.SSN,7,1) + SUBSTRING(ue.SSN,9,1) UNION ALL
SELECT SUBSTRING(ue.SSN,2,1)
+ SUBSTRING(ue.SSN,4,1)
+ SUBSTRING(ue.SSN,6,1)
+ SUBSTRING(ue.SSN,8,1)
+ SPACE(1) + SUBSTRING(ue.SSN,3,1) + SPACE(1) + SUBSTRING(ue.SSN,7,1) + SUBSTRING(ue.SSN,9,1) UNION ALL
SELECT SUBSTRING(ue.SSN,2,1)
+ SUBSTRING(ue.SSN,4,1)
+ SUBSTRING(ue.SSN,6,1)
+ SUBSTRING(ue.SSN,8,1)
+ SPACE(1) + SUBSTRING(ue.SSN,3,1) + SUBSTRING(ue.SSN,5,1) + SPACE(1) + SUBSTRING(ue.SSN,9,1) UNION ALL
SELECT SUBSTRING(ue.SSN,2,1)
+ SUBSTRING(ue.SSN,4,1)
+ SUBSTRING(ue.SSN,6,1)
+ SUBSTRING(ue.SSN,8,1)
+ SPACE(1) + SUBSTRING(ue.SSN,3,1) + SUBSTRING(ue.SSN,5,1) + SUBSTRING(ue.SSN,7,1) + SPACE(1) UNION ALL
SELECT SUBSTRING(ue.SSN,2,1)
+ SUBSTRING(ue.SSN,4,1)
+ SUBSTRING(ue.SSN,6,1)
+ SUBSTRING(ue.SSN,8,1)
+ SUBSTRING(ue.SSN,1,1) + SPACE(2) + SUBSTRING(ue.SSN,7,1) + SUBSTRING(ue.SSN,9,1) UNION ALL
SELECT SUBSTRING(ue.SSN,2,1)
+ SUBSTRING(ue.SSN,4,1)
+ SUBSTRING(ue.SSN,6,1)
+ SUBSTRING(ue.SSN,8,1)
+ SUBSTRING(ue.SSN,1,1) + SPACE(1) + SUBSTRING(ue.SSN,5,1) + SPACE(1) + SUBSTRING(ue.SSN,9,1) UNION ALL
SELECT SUBSTRING(ue.SSN,2,1)
+ SUBSTRING(ue.SSN,4,1)
+ SUBSTRING(ue.SSN,6,1)
+ SUBSTRING(ue.SSN,8,1)
+ SUBSTRING(ue.SSN,1,1) + SPACE(1) + SUBSTRING(ue.SSN,5,1) + SUBSTRING(ue.SSN,7,1) + SPACE(1) UNION ALL
SELECT SUBSTRING(ue.SSN,2,1)
+ SUBSTRING(ue.SSN,4,1)
+ SUBSTRING(ue.SSN,6,1)
+ SUBSTRING(ue.SSN,8,1)
+ SUBSTRING(ue.SSN,1,1) + SUBSTRING(ue.SSN,3,1) + SPACE(2) + SUBSTRING(ue.SSN,9,1) UNION ALL
SELECT SUBSTRING(ue.SSN,2,1)
+ SUBSTRING(ue.SSN,4,1)
+ SUBSTRING(ue.SSN,6,1)
+ SUBSTRING(ue.SSN,8,1)
+ SUBSTRING(ue.SSN,1,1) + SUBSTRING(ue.SSN,3,1) + SPACE(1) + SUBSTRING(ue.SSN,7,1) + SPACE(1) UNION ALL
SELECT SUBSTRING(ue.SSN,2,1)
+ SUBSTRING(ue.SSN,4,1)
+ SUBSTRING(ue.SSN,6,1)
+ SUBSTRING(ue.SSN,8,1)
+ SUBSTRING(ue.SSN,1,1) + SUBSTRING(ue.SSN,3,1) + SUBSTRING(ue.SSN,5,1) + SPACE(2))y
WHERE v.SSN IS NULL)uen
ON (val.SSN2 = uen.SSN2)
GROUP BY Val.SSN,uen.SSN
UNION ALL
SELECT  v.[SSN] , ue.SSN , 0
FROM [dbo].[Validated] v
JOIN [dbo].[UserEntered] ue ON (ue.SSN = v.SSN)
ORDER BY [vSSN], [ueSSN], [status]
```

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Current standing

``````+-----------------------+-----------+----------+-----------+--------+--------+
| Name                  | Time (ms) | Position |   Reads   | Result |  Type  |
+-----------------------+-----------+----------+-----------+--------+--------+
| JAhlen v4             |       320 |        1 |       231 | OK     | SQLCLR |
+-----------------------+-----------+----------+-----------+--------+--------+
| Daniel Ross           |       350 |        2 |       206 | OK     | SSIS   |
+-----------------------+-----------+----------+-----------+--------+--------+
| JAhlen v3             |       370 |          |       231 | Error  | SQLCLR |
+-----------------------+-----------+----------+-----------+--------+--------+
| Matt v2               |       391 |        3 |       231 | OK     | SQLCLR |
+-----------------------+-----------+----------+-----------+--------+--------+
| Matt v3               |       426 |        4 |       237 | OK     | SQLCLR |
+-----------------------+-----------+----------+-----------+--------+--------+
| Matt v1               |       538 |        5 |     1 422 | OK     | T-SQL  |
+-----------------------+-----------+----------+-----------+--------+--------+
| Phil v1e              |       582 |          |     1 994 | OK     | T-SQL  |
+-----------------------+-----------+----------+-----------+--------+--------+
| Matt v1               |       590 |        6 |       237 | OK     | SQLCLR |
+-----------------------+-----------+----------+-----------+--------+--------+
| JAhlen v2             |       652 |        7 |       462 | OK     | SQLCLR |
+-----------------------+-----------+----------+-----------+--------+--------+
| Phil v1c              |       674 |          |     1 537 | OK     | T-SQL  |
+-----------------------+-----------+----------+-----------+--------+--------+
| Phil v1b              |       700 |          |     1 609 | OK     | T-SQL  |
+-----------------------+-----------+----------+-----------+--------+--------+
| Peso v1               |       727 |          |    55 701 | OK     | T-SQL  |
+-----------------------+-----------+----------+-----------+--------+--------+
| JAhlen v1             |       776 |        8 |       462 | OK     | SQLCLR |
+-----------------------+-----------+----------+-----------+--------+--------+
| Peso 2                |       819 |          |   154 153 | OK     | T-SQL  |
+-----------------------+-----------+----------+-----------+--------+--------+
| Steve 1a              |       887 |        9 |    98 500 | OK     | T-SQL  |
+-----------------------+-----------+----------+-----------+--------+--------+
| Phil v1d              |       934 |          |    98 500 | OK     | T-SQL  |
+-----------------------+-----------+----------+-----------+--------+--------+
| Matt v2               |       946 |       10 |    55 755 | OK     | T-SQL  |
+-----------------------+-----------+----------+-----------+--------+--------+
| Phil Factor v1        |     1 900 |          |     5 362 | OK     | T-SQL  |
+-----------------------+-----------+----------+-----------+--------+--------+
| Scot Hauder v1        |     3 122 |       11 |     2 464 | OK     | T-SQL  |
+-----------------------+-----------+----------+-----------+--------+--------+
| Fu Bar v1             | 4 467 583 |          | 1 484 180 | OK     | CURSOR |
+-----------------------+-----------+----------+-----------+--------+--------+
``````

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think SQLCLR are a great addition to the challenge, but other people can't learn from them as they can with posted T-SQL suggestions. What if we agree to post the source for the SQLCLR to be a valid suggestion?
2 Likes 2 ·
I wonder if we ought to put Foo's timing in!
0 Likes 0 ·
Peso - are you doing this in the same way as the harness (x runs with cache drops before the first run, and then taking the median timing)
0 Likes 0 ·
Somewhat. I clear the cache and run the solution 10 times and take the minimum values from SQL Profiler.
0 Likes 0 ·
Shouldn't we use Matt's test harness?
0 Likes 0 ·

--Phil Factor 1A (I'll call this one the 'remove the hopeless and the obvious User-SSNS before you start'

``````--build up the DDL
--clear up if it crashed out with an error
if exists (select 1 from tempdb.information_schema.tables
where Table_Name like '#ValidSSN%')
DROP TABLE #validSSN
if exists (select 1 from tempdb.information_schema.tables
where Table_Name like '#UserSSN%')
if exists (select 1 from tempdb.information_schema.tables
where Table_Name like '#CheckedSSN%')
DROP TABLE #CheckedSSN

/*we'll go for a temporary table since we need to exclude
perfect matches from ambiguous matching. We may want to use
it to remove duplicates if we are doing 'best matching'.*/
CREATE TABLE #CheckedSSN
(
vSSN CHAR(9) NOT NULL,
ueSSN CHAR(9) NOT NULL,
[Status] TINYINT NOT NULL
)
--we put odd and even digits in separate strings for valid SSNs
CREATE TABLE #ValidSSN
(
Valid_ID INT IDENTITY(1, 1),
SSN CHAR(9) NOT NULL primary key,
evens AS (SUBSTRING(SSN, 2, 1)+SUBSTRING(SSN, 4, 1)+SUBSTRING(SSN, 6, 1)
+SUBSTRING(SSN, 8, 1)) PERSISTED,
odds AS (SUBSTRING(SSN, 1, 1)+SUBSTRING(SSN, 3, 1)+SUBSTRING(SSN, 5, 1)
+SUBSTRING(SSN, 7, 1)+SUBSTRING(SSN, 9, 1)) PERSISTED
)

--we put odd and even digits in separate strings for the ones the users enter
(
UserSSN_ID INT IDENTITY(1, 1) primary key,
SSN CHAR(9) NOT NULL,
evens AS (SUBSTRING(SSN, 2, 1)+SUBSTRING(SSN, 4, 1)+SUBSTRING(SSN, 6, 1)
+SUBSTRING(SSN, 8, 1)) PERSISTED,
odds AS (SUBSTRING(SSN, 1, 1)+SUBSTRING(SSN, 3, 1)+SUBSTRING(SSN, 5, 1)
+SUBSTRING(SSN, 7, 1)+SUBSTRING(SSN, 9, 1)) PERSISTED
)

--do some work

--so we can slice up the strings and then index them
INSERT INTO #ValidSSN (SSN)
SELECT [SSN] FROM Validated

--we can remove the 0-error matches
INSERT INTO #CheckedSSN (vSSN, ueSSN, Status)
SELECT Validated.SSN, UserEntered.SSN, 0
FROM UserEntered
INNER JOIN Validated
ON Validated.SSN=UserEntered.SSN

--and we need only waste time on the dud matches
INSERT INTO #UserSSN (SSN)
SELECT UserEntered.SSN
FROM UserEntered
LEFT OUTER JOIN Validated
ON Validated.SSN=UserEntered.SSN
WHERE
Validated.SSN IS NULL--i.e. where we didnt get a match

CREATE INDEX idxValidEvenAndOdd ON #ValidSSN(evens,Odds)
CREATE INDEX idxUsersEvenAndOdd ON #UserSSN(evens,Odds)

--pop in the 1-error matches   where it was an error on the odd side only
INSERT INTO #CheckedSSN (vSSN, ueSSN, Status)
SELECT #ValidSSN.SSN, #UserSSN.SSN, 1 FROM #ValidSSN
WHERE CASE WHEN SUBSTRING(#ValidSSN.odds,1,1)
=SUBSTRING(#UserSSN.odds,1,1) THEN 1 ELSE 0 END+
CASE WHEN SUBSTRING(#ValidSSN.odds,2,1)
=SUBSTRING(#UserSSN.odds,2,1) THEN 1 ELSE 0 END+
CASE WHEN SUBSTRING(#ValidSSN.odds,3,1)
=SUBSTRING(#UserSSN.odds,3,1) THEN 1 ELSE 0 END+
CASE WHEN SUBSTRING(#ValidSSN.odds,4,1)
=SUBSTRING(#UserSSN.odds,4,1) THEN 1 ELSE 0 END+
CASE WHEN SUBSTRING(#ValidSSN.odds,5,1)
=SUBSTRING(#UserSSN.odds,5,1) THEN 1 ELSE 0 END
=4
--pop in the 1-error matches   where it was an error on the even side only
INSERT INTO #CheckedSSN (vSSN, ueSSN, Status)
SELECT #ValidSSN.SSN, #UserSSN.SSN, 1 FROM #ValidSSN
WHERE CASE WHEN SUBSTRING(#ValidSSN.evens,1,1)
=SUBSTRING(#UserSSN.evens,1,1) THEN 1 ELSE 0 END+
CASE WHEN SUBSTRING(#ValidSSN.evens,2,1)
=SUBSTRING(#UserSSN.evens,2,1) THEN 1 ELSE 0 END+
CASE WHEN SUBSTRING(#ValidSSN.evens,3,1)
=SUBSTRING(#UserSSN.evens,3,1) THEN 1 ELSE 0 END+
CASE WHEN SUBSTRING(#ValidSSN.evens,4,1)
=SUBSTRING(#UserSSN.evens,4,1) THEN 1 ELSE 0 END
=3
--pop in the 2-error matches (where no perfect match exists)
-- it was an error on the odd side only
INSERT INTO #CheckedSSN (vSSN, ueSSN, Status)
SELECT #ValidSSN.SSN, #UserSSN.SSN, 2 FROM #ValidSSN
left outer join #CheckedSSN on #UserSSN.SSN =#CheckedSSN.ueSSN and status=0
WHERE CASE WHEN SUBSTRING(#ValidSSN.odds,1,1)
=SUBSTRING(#UserSSN.odds,1,1) THEN 1 ELSE 0 END+
CASE WHEN SUBSTRING(#ValidSSN.odds,2,1)
=SUBSTRING(#UserSSN.odds,2,1) THEN 1 ELSE 0 END+
CASE WHEN SUBSTRING(#ValidSSN.odds,3,1)
=SUBSTRING(#UserSSN.odds,3,1) THEN 1 ELSE 0 END+
CASE WHEN SUBSTRING(#ValidSSN.odds,4,1)
=SUBSTRING(#UserSSN.odds,4,1) THEN 1 ELSE 0 END+
CASE WHEN SUBSTRING(#ValidSSN.odds,5,1)
=SUBSTRING(#UserSSN.odds,5,1) THEN 1 ELSE 0 END
=3
and #CheckedSSN.ueSSN is null
--pop in the 2-error matches (where no perfect match exists)
--where it was an error on the even side only
INSERT INTO #CheckedSSN (vSSN, ueSSN, Status)
SELECT #ValidSSN.SSN, #UserSSN.SSN, 2  FROM #ValidSSN
left outer join #CheckedSSN on #UserSSN.SSN =#CheckedSSN.ueSSN and status=0
--the outer join is to eliminate those that had perfect matched
WHERE CASE WHEN SUBSTRING(#ValidSSN.evens,1,1)
=SUBSTRING(#UserSSN.evens,1,1) THEN 1 ELSE 0 END+
CASE WHEN SUBSTRING(#ValidSSN.evens,2,1)
=SUBSTRING(#UserSSN.evens,2,1) THEN 1 ELSE 0 END+
CASE WHEN SUBSTRING(#ValidSSN.evens,3,1)
=SUBSTRING(#UserSSN.evens,3,1) THEN 1 ELSE 0 END+
CASE WHEN SUBSTRING(#ValidSSN.evens,4,1)
=SUBSTRING(#UserSSN.evens,4,1) THEN 1 ELSE 0 END
=2
and #CheckedSSN.ueSSN is null
--print out the results
Select vSSN,
ueSSN,
[Status] from #checkedSSN order by vSSN, ueSSN, Status
---clean up
DROP TABLE #validSSN
DROP TABLE #CheckedSSN
``````

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Good job Phil, I also am working on a solution that reduces the number of effective rows acted upon. With only 8K rows returned we should see some sub-second entries soon
0 Likes 0 ·
The trick here is that any SSNs that have any hope of matching must have a complete match on either a string made up of the odd characters, or one of the even characters. You can safely junk the rest. This SQL is over-complicated because I'd wrongly assumed that we were doing a 'best-match' algorithm, and I altered it at the last minute. When I get a moment, I'll tidy it up.
0 Likes 0 ·

I call this the pure SQL CLR solution... J

``````select * from dbo.match()
``````

Oh, by the way you need to add the SQL CLR assembly first...

``````CREATE ASSEMBLY [SSNMatch]
AUTHORIZATION [dbo]
WITH PERMISSION_SET = SAFE

GO

CREATE FUNCTION [dbo].[Match]()
RETURNS  TABLE (
[vSSN] [nchar](9) NULL,
[ueSSN] [nchar](9) NULL,
[Status] [tinyint] NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SSNMatch].[UserDefinedFunctions].[Match]
GO
``````

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Vad kul att se dig här!
0 Likes 0 ·
And for you non-swedish speakers; "Great to see you here!".
0 Likes 0 ·
I get this error message trying to run the SQLCLR. "Msg 6532, Level 16, State 49, Line 1 .NET Framework execution was aborted by escalation policy because of out of memory. System.Threading.ThreadAbortException: Thread was being aborted. System.Threading.ThreadAbortException: at System.Collections.BitArray..ctor(Int32 length, Boolean defaultValue) at UserDefinedFunctions.Match()"
0 Likes 0 ·
Om inte någon annan skriver ett snabbare CLR lösning, är du dömd att få pris för bästa CLR-baserad lösning. Välkommen till tävlingen, och tack för att komma in.
0 Likes 0 ·
Thanks. My SQL CLR solution works on my laptop (64-bit SQL Server 2008 R2 RTM with default settings) and takes approx 2 300 ms. If necessary I can make some changes so it takes less memory (but will be a bit slower).
0 Likes 0 ·

New version (more memory conservative, but slower). I call this the pure SQL CLR solution 2... J

``````select * from dbo.match()
``````

Code for the SQL CLR assembly...

``````CREATE ASSEMBLY [SSNMatch]
AUTHORIZATION [dbo]
WITH PERMISSION_SET = SAFE

GO

CREATE FUNCTION [dbo].[Match]()
RETURNS  TABLE (
[vSSN] [nchar](9) NULL,
[ueSSN] [nchar](9) NULL,
[Status] [tinyint] NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SSNMatch].[UserDefinedFunctions].[Match]
GO
``````
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This is 100 ms faster than v1.
0 Likes 0 ·

Matt v1

Titled the 'This probably really won't scale very well, but it's fast for the given data' version

``````DECLARE @UserEnteredTmp TABLE (
[u1] [tinyint] NOT NULL, [u2] [tinyint] NOT NULL, [u3] [tinyint] NOT NULL, [u4] [tinyint] NOT NULL, [u5] [tinyint] NOT NULL,
[u6] [tinyint] NOT NULL, [u7] [tinyint] NOT NULL, [u8] [tinyint] NOT NULL, [u9] [tinyint] NOT NULL, [odds] [int] NOT NULL,
[evens] [smallint] NOT NULL, [ssn] [char] (9) )
DECLARE @ValidatedTmp TABLE (
[u1] [tinyint] NOT NULL, [u2] [tinyint] NOT NULL, [u3] [tinyint] NOT NULL, [u4] [tinyint] NOT NULL, [u5] [tinyint] NOT NULL,
[u6] [tinyint] NOT NULL, [u7] [tinyint] NOT NULL, [u8] [tinyint] NOT NULL, [u9] [tinyint] NOT NULL, [odds] [int] NOT NULL,
[evens] [smallint] NOT NULL, [ssn] [char] (9) )

INSERT INTO @UserEnteredTmp ([u1], [u2], [u3], [u4], [u5], [u6], [u7], [u8], [u9], [odds], [evens], [ssn])
SELECT [u1], [u2], [u3], [u4], [u5], [u6], [u7], [u8], [u9], [u1] + [u3] + [u5] + [u7] + [u9], [u2] + [u4] + [u6] + [u8], [SSN] FROM
(
SELECT SUBSTRING(SSN, 1, 1) AS u1, SUBSTRING(SSN, 2, 1) AS u2, SUBSTRING(SSN, 3, 1) AS u3, SUBSTRING(SSN, 4, 1) AS u4,
SUBSTRING(SSN, 5, 1) AS u5, SUBSTRING(SSN, 6, 1) AS u6, SUBSTRING(SSN, 7, 1) AS u7, SUBSTRING(SSN, 8, 1) AS u8,
SUBSTRING(SSN, 9, 1) AS u9, [SSN]
FROM  (SELECT [SSN] AS SSN
FROM [dbo].[UserEntered]
WHERE [SSN] NOT IN
(SELECT [SSN]
FROM [dbo].[Validated])
) idat
) odat

INSERT INTO @ValidatedTmp ([u1], [u2], [u3], [u4], [u5], [u6], [u7], [u8], [u9], [odds], [evens], [ssn])
SELECT [u1], [u2], [u3], [u4], [u5], [u6], [u7], [u8], [u9], [u1] + [u3] + [u5] + [u7] + [u9], [u2] + [u4] + [u6] + [u8], [ssn] FROM
(
SELECT SUBSTRING(SSN, 1, 1) AS u1, SUBSTRING(SSN, 2, 1) AS u2, SUBSTRING(SSN, 3, 1) AS u3, SUBSTRING(SSN, 4, 1) AS u4,
SUBSTRING(SSN, 5, 1) AS u5, SUBSTRING(SSN, 6, 1) AS u6, SUBSTRING(SSN, 7, 1) AS u7, SUBSTRING(SSN, 8, 1) AS u8,
SUBSTRING(SSN, 9, 1) AS u9, [SSN]
FROM [dbo].[Validated]
) odat

SELECT   [vSSN], [ueSSN], [Status]
FROM     (SELECT [SSN] AS ueSSN, [SSN] AS vSSN, 0 AS [Status]
FROM   (SELECT [dbo].[UserEntered].[SSN] AS SSN
FROM   [dbo].[UserEntered]
WHERE  [SSN] IN (SELECT [SSN]
FROM   [dbo].[Validated])) AS idat
UNION ALL
SELECT [ueSSN], [vSSN], [Status]
FROM   (SELECT [UserEnteredTmp].[ssn] AS ueSSN, [ValidatedTmp].[ssn] AS vSSN,
CASE WHEN [ValidatedTmp].[u2] = [UserEnteredTmp].[u2] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u4] = [UserEnteredTmp].[u4] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u6] = [UserEnteredTmp].[u6] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u8] = [UserEnteredTmp].[u8] THEN 0 ELSE 1 END AS [Status]
FROM   @UserEnteredTmp AS UserEnteredTmp INNER JOIN
@ValidatedTmp AS ValidatedTmp ON [UserEnteredTmp].[odds] = [ValidatedTmp].[odds]) AS idat
WHERE  [Status] < 3
UNION ALL
SELECT [ueSSN], [vSSN], [Status]
FROM   (SELECT [UserEnteredTmp].[ssn] AS ueSSN, [ValidatedTmp].[ssn] AS vSSN,
CASE WHEN [ValidatedTmp].[u1] = [UserEnteredTmp].[u1] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u3] = [UserEnteredTmp].[u3] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u5] = [UserEnteredTmp].[u5] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u7] = [UserEnteredTmp].[u7] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u9] = [UserEnteredTmp].[u9] THEN 0 ELSE 1 END AS [Status]
FROM   @UserEnteredTmp AS UserEnteredTmp INNER JOIN
@ValidatedTmp AS ValidatedTmp ON [UserEnteredTmp].[evens] = [ValidatedTmp].[evens]) AS idat
WHERE  [Status] < 3
) AS idd
ORDER BY [vSSN], [ueSSN], [Status];
``````

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'll try this after I run Fu's code again to get read count.
0 Likes 0 ·
... and get some sleep while it's running! :)
0 Likes 0 ·
... Just updated the order by clause
0 Likes 0 ·

Matt CLR v1

The 'it was going to happen' edition

``````CREATE ASSEMBLY [Challenge5]
AUTHORIZATION [dbo]
FROM
WITH PERMISSION_SET = SAFE
GO
ALTER ASSEMBLY [Challenge5]
WITH VISIBILITY = ON

GO
CREATE PROCEDURE [dbo].[DoSSNMatching]
AS EXTERNAL NAME [Challenge5].[StoredProcedures].[DoSSNMatching]
GO
``````

And then

``````EXEC dbo.[DoSSNMatching]
``````
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nice ~653
0 Likes 0 ·

Matt v2

The 'Single query to bring happiness to Peso' version :)

Only very slightly different to v1.

``````WITH UserEnteredTmp ([u1], [u2], [u3], [u4], [u5], [u6], [u7], [u8], [u9], [odds], [evens], [ssn])
AS
(
SELECT [u1], [u2], [u3], [u4], [u5], [u6], [u7], [u8], [u9], [u1] + [u3] + [u5] + [u7] + [u9], [u2] + [u4] + [u6] + [u8], [SSN] FROM
(
SELECT SUBSTRING(SSN, 1, 1) AS u1, SUBSTRING(SSN, 2, 1) AS u2, SUBSTRING(SSN, 3, 1) AS u3, SUBSTRING(SSN, 4, 1) AS u4,
SUBSTRING(SSN, 5, 1) AS u5, SUBSTRING(SSN, 6, 1) AS u6, SUBSTRING(SSN, 7, 1) AS u7, SUBSTRING(SSN, 8, 1) AS u8,
SUBSTRING(SSN, 9, 1) AS u9, [SSN]
FROM  (SELECT [SSN] AS SSN
FROM [dbo].[UserEntered]
WHERE [SSN] NOT IN
(SELECT [SSN]
FROM [dbo].[Validated])
) idat
) odat
),
ValidatedTmp ([u1], [u2], [u3], [u4], [u5], [u6], [u7], [u8], [u9], [odds], [evens], [ssn])
AS
(
SELECT [u1], [u2], [u3], [u4], [u5], [u6], [u7], [u8], [u9], [u1] + [u3] + [u5] + [u7] + [u9], [u2] + [u4] + [u6] + [u8], [ssn] FROM
(
SELECT SUBSTRING(SSN, 1, 1) AS u1, SUBSTRING(SSN, 2, 1) AS u2, SUBSTRING(SSN, 3, 1) AS u3, SUBSTRING(SSN, 4, 1) AS u4,
SUBSTRING(SSN, 5, 1) AS u5, SUBSTRING(SSN, 6, 1) AS u6, SUBSTRING(SSN, 7, 1) AS u7, SUBSTRING(SSN, 8, 1) AS u8,
SUBSTRING(SSN, 9, 1) AS u9, [SSN]
FROM [dbo].[Validated]
) odat
)
SELECT   [vSSN], [ueSSN], [Status]
FROM     (SELECT [SSN] AS ueSSN, [SSN] AS vSSN, 0 AS [Status]
FROM   (SELECT [dbo].[UserEntered].[SSN] AS SSN
FROM   [dbo].[UserEntered]
WHERE  [SSN] IN (SELECT [SSN]
FROM   [dbo].[Validated])) AS idat
UNION ALL
SELECT [ueSSN], [vSSN], [Status]
FROM   (SELECT [UserEnteredTmp].[ssn] AS ueSSN, [ValidatedTmp].[ssn] AS vSSN,
CASE WHEN [ValidatedTmp].[u2] = [UserEnteredTmp].[u2] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u4] = [UserEnteredTmp].[u4] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u6] = [UserEnteredTmp].[u6] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u8] = [UserEnteredTmp].[u8] THEN 0 ELSE 1 END AS [Status]
FROM   UserEnteredTmp INNER JOIN
ValidatedTmp ON [UserEnteredTmp].[odds] = [ValidatedTmp].[odds]) AS idat
WHERE  [Status] < 3
UNION ALL
SELECT [ueSSN], [vSSN], [Status]
FROM   (SELECT [UserEnteredTmp].[ssn] AS ueSSN, [ValidatedTmp].[ssn] AS vSSN,
CASE WHEN [ValidatedTmp].[u1] = [UserEnteredTmp].[u1] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u3] = [UserEnteredTmp].[u3] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u5] = [UserEnteredTmp].[u5] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u7] = [UserEnteredTmp].[u7] THEN 0 ELSE 1 END + CASE WHEN [ValidatedTmp].[u9] = [UserEnteredTmp].[u9] THEN 0 ELSE 1 END AS [Status]
FROM   UserEnteredTmp INNER JOIN
ValidatedTmp ON [UserEnteredTmp].[evens] = [ValidatedTmp].[evens]) AS idat
WHERE  [Status] < 3
) AS idd
ORDER BY [vSSN], [ueSSN], [Status];
``````
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Very similar to my "one-liner" !
0 Likes 0 ·

I have a good Unusual/experimental attempt that I created for s#\$%'s and giggles, however all of the logic is entirely in SSIS 2005.

If I am allowed to enter this, how do I get this package to you Peso? I can copy the XML here then you will have to set the connection, but it is long.

the package has only two components; an execute SQL task which creates the results table, and a data flow task.

In the data flow task there are only 2 data sources and 1 destination, however there are quite a few merge join components and conditional split components. note, there are no script components

It runs in BIDS for about 2 seconds on my PC, but on a gutsy server in might be slightly faster.

It's raining green baby!

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I hope there is a way to include this - because I would say that fits directly in unusual/experimental - top job!
0 Likes 0 ·
I think it would qualify as unusual. I'd love to see it.
0 Likes 0 ·
Email me the zipped project, and I'll test it with SQL Profiler.
0 Likes 0 ·
I wonder if Fuzzy Lookup would do the work?
0 Likes 0 ·
Have you found my email adress?
0 Likes 0 ·

The pure SQL CLR solution v3

``````CREATE ASSEMBLY [SSNMatch]
AUTHORIZATION [dbo]
WITH PERMISSION_SET = SAFE

GO

CREATE PROCEDURE [dbo].[MatchProc]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SSNMatch].[StoredProcedures].[MatchProc]
GO
``````

and then execute it:

``````EXEC dbo.[MatchProc]
``````

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Now it takes less than 400 ms on my laptop.
0 Likes 0 ·
Keeping me on my toes - love it :)
0 Likes 0 ·
Unfortenately, it doesn't produce the correct result.
0 Likes 0 ·
Really??? I will check.
0 Likes 0 ·
It is the sorting that is missong. Sort by vSSN, ueSSN and [Status] and your result is ok.
0 Likes 0 ·

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.