MERGE statement match problem 2

[TheBugSlayer]

Hello everyone.

I have had to split a MERGE statement into two separate ones because I could not figure out why duplicates were added after the second execution. The source of the first MERGE, code commented in my statement, is providers with an NPI number; 2032 in total. The source of the second MERGE below is 2308 providers without an NPI number. So I am expecting to see 4340 providers in the target table. However there are only 4231 providers after both statements are executed.

In the source table (for both MERGE source queries), some providers exist WITH and WITHOUT an NPI number. I run the first MERGE and all is fine. However the second MERGE only inserts apparently less records than the 2308 expected because the total at the end of the execution is 4231 instead of 4340.

I have not been able to figure out how to modify the ON and WHEN MATCHED clauses to get the full complement of providers in the target table. Below is my code:

	MERGE INTO dbo.StageReferringProvider target
		USING (
				 SELECT temp.LastName, temp.FirstName, temp.MiddleName, temp.Degree, LTRIM(RTRIM(NPI)) AS NPI, temp.Sub, temp.PracticeName,  CHECKSUM(temp.PracticeName) AS PracticeHash
				 FROM (    
				            --Providers WITH an NPI number
						   --SELECT ROW_NUMBER() OVER (PARTITION BY NPI ORDER BY NPI) AS RowNumber, *
						   --FROM ufnBernhardt_ReferringProviders()
						   --WHERE LEN(NPI) > 0
						   --UNION
						   SELECT ROW_NUMBER() OVER (PARTITION BY LastName, FirstName ORDER BY LastName, FirstName) AS RowNumber, *
						   FROM ufnBernhardt_ReferringProviders() 
						   WHERE LEN(NPI) <= 0 AND (LEN(LastName) > 0 AND LEN(FirstName) > 0)
					  ) AS temp
				 WHERE temp.RowNumber = 1
			  ) AS source
		[b][COLOR=red] ON target.LastName = source.LastName AND target.FirstName = source.FirstName
		WHEN MATCHED 
			 AND LEN(target.NPI) <= 0 AND (target.MiddleName <> source.MiddleName OR target.Degree <> source.Degree [/color])[/b]
			THEN UPDATE SET target.MiddleName = source.MiddleName, target.Degree = source.Degree
		WHEN NOT MATCHED THEN INSERT VALUES(source.LastName, source.FirstName, source.MiddleName, source.Degree, LTRIM(RTRIM(source.NPI)), GETDATE(), GETDATE())
		OUTPUT COALESCE(Inserted.RID, Deleted.RID), $action, source.PracticeHash, source.PracticeName, source.Sub INTO #MergeReferringProviderOutput;

Any help is greatly appreciated.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

 

[TamarGranor]

The ON clause determines whether a record is matched or not. The additional conditions you put into WHEN MATCHED determine whether you do something with a matched record or now. That is, as written, your code doesn't do anything with records where the names matched but this condition is not true:

LEN(target.NPI) <= 0 AND (target.MiddleName <> source.MiddleName OR target.Degree <> source.Degree )

Do you mean to do something with those, as well? If so, add a second WHEN MATCHED clause to handle them.

Tamar

 

[fredericofonseca]

with regards the duplicates the following code should identify those (untested so may give syntax errors)

;with temp_a as
(select *
  FROM (    
          --Providers WITH an NPI number
          SELECT ROW_NUMBER() OVER (PARTITION BY NPI ORDER BY NPI) AS RowNumber, *
          FROM ufnBernhardt_ReferringProviders()
          WHERE LEN(NPI) > 0
          UNION
          SELECT ROW_NUMBER() OVER (PARTITION BY LastName, FirstName ORDER BY LastName, FirstName) AS RowNumber, *
          FROM ufnBernhardt_ReferringProviders() 
          WHERE LEN(NPI) <= 0 AND (LEN(LastName) > 0 AND LEN(FirstName) > 0)
       ) AS temp
  WHERE temp.RowNumber = 1
),
temp_b as (
    select  LastName
          , FirstName
    from ( select *
           FROM (    
                     --Providers WITH an NPI number
                     SELECT ROW_NUMBER() OVER (PARTITION BY NPI ORDER BY NPI) AS RowNumber, *
                     FROM ufnBernhardt_ReferringProviders()
                     WHERE LEN(NPI) > 0
                     UNION
                     SELECT ROW_NUMBER() OVER (PARTITION BY LastName, FirstName ORDER BY LastName, FirstName) AS RowNumber, *
                     FROM ufnBernhardt_ReferringProviders() 
                     WHERE LEN(NPI) <= 0 AND (LEN(LastName) > 0 AND LEN(FirstName) > 0)
                ) AS temp
           WHERE temp.RowNumber = 1
           ) temp1
    group by LastName
           , FirstName
) 
select t1.*
from temp_b t1
inner join temp_a t2
on t2.LastName = t1.LastName
and t2.FirstName = t1.FirstName
order by t1.LastName
       , T1.FirstName

reason why you get duplicates is that both sides of the union may eventually return duplicate first/last names, or that the first part of the union (which assignes rownum based on NPI also have duplicate first/last names

It may be the case that you should do a further rownum based on the one you have already with PARTITION BY LastName, FirstName and then retrieve the rownum of those.


As for the merge itself I may be wrong but your idea of what it is doing may be incorrect.


what you have is the same as the following (in terms of selection of records.

Join query for matched that result in an update

select *
from dbo.StageReferringProvider t1
left outer join source t2 -- assume this is the query contained on your source above
on t1.LastName = t2.LastName
and t1.FirstName = t2.FirstName
-- this is the match criteria
    where t2.FirstName is not null
-- this is the update criteria
   AND LEN(target.NPI) <= 0 
   AND (target.MiddleName <> source.MiddleName OR target.Degree <> source.Degree )

Join query for unmatched that result in an insert

select *
from dbo.StageReferringProvider t1
left outer join source t2 -- assume this is the query contained on your source above
on t1.LastName = t2.LastName
and t1.FirstName = t2.FirstName
-- this is the (un)match criteria
   where t2.FirstName is null

Join query for matched that do not result in an update

select *
from dbo.StageReferringProvider t1
left outer join source t2 -- assume this is the query contained on your source above
on t1.LastName = t2.LastName
and t1.FirstName = t2.FirstName
-- this is the match criteria
    where t2.FirstName is not null
-- this is the update criteria
   AND not (LEN(target.NPI) <= 0 
      AND (target.MiddleName <> source.MiddleName OR target.Degree <> source.Degree ))

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[TheBugSlayer]

Thank you guys for your help. I just resumed work from a vacation. When I get time I will go through all your posts and report what I find.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).