SQL - Create records without Duplicate

[WordTechinc]

I am a beginer SQL. Please advise me. I would to know how to change to create one record by field name(LICENSE_ID). There are multi records with field name [Prof Code] by field name [ICENSE_ID].


-- generate Mailing list with multi job numbers as master job number --

CREATE PROC [dbo].[INRv2_GetMailingList]
(
@AllJobCodes nvarchar(2000),
@AllZipCodes NText --nvarchar(2000)
)
AS



declare @Code1 varchar(100)
set @Code1='''M'',''S'',''X'''
-- Get PL Details Prospect Responder with highest Responder count

exec('

DECLARE @MasterJob TABLE
(
jobno VARCHAR(5),
MasterJobNo VARCHAR(5)
)


INSERT INTO @MasterJob
Select jobno,MasterJobNo
from INR_JobShedule where jobNo in('+ @AllJobCodes + ')


--===???????????????????????????????????????????================================================
-- Need Help here--
-- I want to make chane here - I need to keep all these fields but distinct by LICENSE_ID --
-- This returns multiful records because by LICENSE_ID can have multi [PROF CODE]
-- ?????????????????????????????????????????????

Select
distinct

[First Name] + '' '' + [Last Name] as NAME,
[company_name] COMPANY,
[Contact_Title] TITLE,
[address 1] ADDRESS1,
[address 2] ADDRESS2,
[address 3] ADDRESS3,
[address 4] ADDRESS4,
[city] CITY,
[state] STATE,
[5 digit zip] ZIP,
[last four digits of zip (after the -)] [ZIP + 4],
[delivery point barcode] DPBC,
[check digit] [CHECK], MJ.[Masterjobno] [JOBNO],
[unique id] LICENSE_ID,
StudentID STUDENT_ID,
''L'' [TYPE],

ProfCode [PROF_CODE],
''PRIORITY CODE:''+ISNULL(LTRIM(RTRIM(MJ.[Masterjobno])),'''')+''*''+''L''+CONVERT(VARCHAR(12),[Unique ID]) AS [KEYLINE],IMB

INTO #A

--===========================================================================================================

from INR_MailingList IM INNER JOIN @MasterJob MJ
ON IM.jobno=MJ.jobNo
where [5 digit zip] in('+ @AllZipCodes + ')Order By IM.[unique id]

Select License_Id,Count(License_Id) COUNTED INTO #Duplicate From #A
Group By License_Id
Having Count(License_Id)>1

Select DISTINCT A.* INTO #Mailing from #A A,#Duplicate D where
A.License_Id=D.License_Id and Prof_Code is not Null

DELETE A from #A A INNER JOIN #Duplicate D ON
A.License_Id=D.License_Id

INSERT INTO #A
Select DISTINCT * from #Mailing

Select * from #A

DELETE INR_MailingAuditTable
FROM INR_MailingAuditTable INNER JOIN
#A ON INR_MailingAuditTable.JobNo = #A.JobNo AND INR_MailingAuditTable.UniqueID = #A.[License_ID]

INSERT INTO INR_MailingAuditTable(JobNo,CorresDate,UniqueID)
SELECT
#A.Jobno,getdate(), #A.License_ID
FROM
#A

')

GO

 

[markros]

Does it matter which priority code to pick?

In any case, please read and understand this blog

http://blogs.lessthandot.com/index....rver/including-an-aggregated-column-s-related

exploring this problem in details.

 

[WordTechinc]

after modify, it only add 1 records from #Mailing out of 92 records.


I changed from ;

INSERT INTO #A
Select DISTINCT * from #Mailing

I changed into ;

INSERT INTO #A
SELECT TOP 1 NM.* from #Mailing NM
ORDER BY NM.License_id

 

[markros]

Did you want 1 record per each License? Did you read and try to understand the blog I gave a link to?

 

[WordTechinc]

Thank you MarKros. You are my life saver.

 

[markros]

You're welcome.