May 21, 2018 at 10:41 am
--Here it returns 590
SELECT DISTINCT
LEFT(CAST(al.Name AS VARCHAR(25)), CASE WHEN charindex(' ', CAST(al.Name AS VARCHAR(25))) = 0 THEN
LEN(CAST(al.Name AS VARCHAR(25))) ELSE charindex(' ', CAST(al.Name AS VARCHAR(25))) - 1 END) as FristName,
RIGHT(al.Name, CASE WHEN charindex(' ', al.Name) = 0 THEN
LEN(CAST(al.Name AS VARCHAR(25))) ELSE charindex(' ', CAST(al.Name AS VARCHAR(25))) - 1 END) as LastNmae,
Right(al.[SSN],4) AS SSN
FROM [dbo].[AllLoans] al
--Here I get 16534
SELECT DISTINCT
LEFT(CAST(al.Name AS VARCHAR(25)), CASE WHEN charindex(' ', CAST(al.Name AS VARCHAR(25))) = 0 THEN
LEN(CAST(al.Name AS VARCHAR(25))) ELSE charindex(' ', CAST(al.Name AS VARCHAR(25))) - 1 END) as FristName,
RIGHT(al.Name, CASE WHEN charindex(' ', al.Name) = 0 THEN
LEN(CAST(al.Name AS VARCHAR(25))) ELSE charindex(' ', CAST(al.Name AS VARCHAR(25))) - 1 END) as LastNmae,
Right(al.[SSN],4) AS SSN
FROM [dbo].[AllLoans] al INNER JOIN [DbInfo] di
ON Right(al.[SSN],4) = RIGHT(cast(di.ssn_no as nvarchar(9)), 4)
May 21, 2018 at 10:50 am
Your INTERSECT query is comparing all of the columns whereas the JOIN version compares only right(SSN,4) ... explaining why the second returns more matches.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 22, 2018 at 9:47 am
Basically, matching only on the last 4 isn't going to give you a correct result, as two people can easily have the last 4 of their SSN be the same. That kind of matching is going to generate what amounts to tying records from one person to records of some other person. Bad idea.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
May 22, 2018 at 10:00 am
Also, INTERSECT matches on NULL = NULL whereas Joins usually do not (default server setting).
--Jeff Moden
May 22, 2018 at 10:02 am
Thanks guys this all really helps
May 22, 2018 at 5:35 pm
itmasterw 60042 - Tuesday, May 22, 2018 10:02 AMThanks guys this all really helps
one last item - from MS docs:
INTERSECT returns distinct rows that are output by both the left and right input queries operator.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply