SQLTeam.com | Weblogs | Forums

Adding two SQL statements together and De-Dupping

sql2012

#1

Hello All

I am new at SQL and I am just trying to add two SQL statements together which are below. The first table is a basic make table pulling in only fields I need and then the second table is pulling the same information but appending it to the first table I made in Step 1 . Also when bringing the queries together I am trying to figure out a way to group everything by NPI so I can eliminate Duplicates . Any help please again I am new at this been doing this for about a week now

/first step/
/Select
[NPI],
[Last Name],
[First Name],
[Middle Name],
Suffix,
Gender,
[Spoken Languages]
Into [Provider Table]
From sylvia.dbo.UNIQUEACN
/

/Second step appending PCCN Providers/
Insert into [sylvia].dbo.[provider Table] ( NPI, [Last Name], [First Name], [Middle Name], Suffix, Gender, [spoken languages] )
Select sylvia.dbo.[PCCNProviders].NPI, sylvia.dbo.PCCNProviders.[Last Name],sylvia.dbo.PCCNProviders.[First Name], sylvia.dbo.PCCNProviders.[Middle Name], sylvia.dbo.PCCNProviders.suffix, sylvia.dbo.PCCNProviders.gender, sylvia.dbo.PCCNProviders.[Spoken Languages]
From sylvia.dbo.[PCCNproviders];


#2

If you get multiple rows for a given value of NPI, there may be different values for other columns. For example, for a given value of NPI, one row may have Spoken Language = 'English' and another row may have Spoken Language = 'Spanish'. Which of those two rows do you want to pick? What is the rule for picking that?

Once you decide that, it is not hard to pick one row per NPI, for example, like this:

SELECT *
FROM
(       
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY NPI
			ORDER BY (SELECT NULL)) AS RN
	FROM
		[sylvia].dbo.[provider Table]
) AS s
WHERE RN = 1   

Here I am picking a random row for a given value of NPI via the use of SELECT NULL in the row_number function. More likely than not, that is not what you want to do. So decide on an ordering scheme to pick one of the many rows for a given value of NPI.


#3

Ah, I see then I would need to choose my criteria then? That is my issue I will figure it out thank you so much