Solved

How to eliminate duplicate records based on the field ACCOUNT within a stored procedure?

Posted on 2011-03-10
4
174 Views
Last Modified: 2012-05-11
I am developing an Access application using Access 2003 with an ADP type file with SQL Server 2005.
I execute a stored procedure to create unique records based on the field "ACCOUNT" in the following stored procedure.
Somehow I am coming up with some duplicate "ACCOUNT" records.

Do you know how I would modify the following stored procedure in the CODE section below, to eliminate DUPE records based on the field ACCOUNT?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[procPWMUniqueMod]
AS

If  Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'dbo.tblPWMMatchByDatesCMod' AND TYPE = 'U')
DELETE FROM dbo.tblPWMMatchByDatesCMod

INSERT INTO dbo.tblPWMMatchByDatesCMod
SELECT t1.ACCOUNT,max(t1.DEC06) as DEC06, max(t1.JAN07) as JAN07,max(t1.FEB07) as FEB07,max(t1.MAR07) as MAR07,
max(t1.APR07) as APR07,max(t1.MAY07) as MAY07,max(t1.JUN07) as JUN07,max(t1.JUL07) as JUL07,max(t1.AUG07) as AUG07,max(t1.SEP07) as SEP07,
max(t1.OCT07) as OCT07,max(t1.NOV07) as NOV07,max(t1.DEC07) as DEC07,max(t1.JAN08) as JAN08,max(t1.FEB08) as FEB08,max(t1.MAR08) as MAR08,
max(t1.APR08) as APR08,max(t1.MAY08) as MAY08,max(t1.JUN08) as JUN08,max(t1.JUL08) as JUL08,max(t1.AUG08) as AUG08,max(t1.SEP08) as SEP08,
max(t1.OCT08) as OCT08,max(t1.NOV08) as NOV08,max(t1.DEC08) as DEC08,max(t1.JAN09) as JAN09,max(t1.FEB09) as FEB09,max(t1.MAR09) as MAR09,
max(t1.APR09) as APR09,max(t1.MAY09) as MAY09,max(t1.JUN09) as JUN09,max(t1.JUL09) as JUL09,max(t1.AUG09) as AUG09,max(t1.SEP09) as SEP09,
max(t1.OCT09) as OCT09,max(t1.NOV09) as NOV09,max(t1.DEC09) as DEC09,max(t1.JAN10) as JAN10,max(t1.FEB10) as FEB10,max(t1.MAR10) as MAR10,
max(t1.APR10) as APR10,max(t1.MAY10) as MAY10,max(t1.JUN10) as JUN10,max(t1.JUL10) as JUL10,max(t1.AUG10) as AUG10,max(t1.SEP10) as SEP10,
max(t1.OCT10) as OCT10,max(t1.NOV10) as NOV10,max(t1.DEC10) as DEC10,max(t1.JAN11) as JAN11,max(t1.FEB11) as FEB11,max(t1.MAR11) as MAR11,
max(t1.APR11) as APR11,max(t1.MAY11) as MAY11,max(t1.JUN11) as JUN11,max(t1.JUL11) as JUL11,max(t1.AUG11) as AUG11,max(t1.SEP11) as SEP11,
max(t1.OCT11) as OCT11,max(t1.NOV11) as NOV11,max(t1.DEC11) as DEC11,
v1.Mailroom_Address1,v1.Mailroom_Address2,v1.Mailroom_Address3,v1.Mailroom_Address4,
v1.Mailroom_Address5,t1.C_TAPS_ACCOUNT,t1.C_LNAME,t1.C_NAME_ADDR_TYPE,t1.C_ADDRESS1,t1.C_ADDRESS2,t1.C_ADDRESS3,t1.C_ADDRESS4,t1.CITY,
t1.STATE,t1.ZIP,t1.Country,t1.C_ADD_COUNTRY,t1.B_BNAME,t1.B_WHO,t1.B_BROKER_ID,t1.C_ON_CLIENT_LINK,t1.C_TAX_ID,t1.C_OP_DATE,t1.C_TERM_DATE,t1.C_MARKET_VALUE,
t1.C_MAX_ACTIVITY_DATE,t1.C_CHANGE_OF_ADDR,t1.SUPPRESSION_DATE,t1.DocumentType,t1.CLIENT_ID,t1.ADDR_NAME_FIRST_A,
t1.ADDR_NAME_MIDDLE_A,t1.ADDR_NAME_LAST_A,t1.ADDR_NAME_SUFFIX_A,t1.ADDR_STREET_NUMBERADDRESS_A,t1.ADDR_CITY_A,t1.ADDR_STATE_A,
t1.ADDR_ZIP5ZIPCODE_A,t1.ADDR_TYPE_A,t1.ADDR_CONFIDENCE_A,t1.PH_PHONE_A_1,t1.PH_LISTING_NAME_A_1,
t1.PH_PHONE_TYPE_A_1,t1.PH_PHONE_A_2,t1.PH_LISTING_NAME_A_2,t1.PH_PHONE_TYPE_A_2,t1.PH_PHONE_A_3,t1.PH_LISTING_NAME_A_3,
t1.PH_PHONE_TYPE_A_3 

FROM dbo.tblPWMMatchByDatesMod 
t1 INNER JOIN (SELECT a1.account,a1.Mailroom_Address1,a1.Mailroom_Address2,a1.Mailroom_Address3,a1.Mailroom_Address4,a1.
Mailroom_Address5,a1.C_TAPS_ACCOUNT,a1.C_LNAME,a1.C_NAME_ADDR_TYPE,a1.C_ADDRESS1,a1.C_ADDRESS2,a1.C_ADDRESS3,a1.C_ADDRESS4,
a1.CITY,a1.STATE,a1.ZIP,a1.COUNTRY,a1.B_BNAME,a1.B_WHO,a1.B_BROKER_ID,a1.C_ON_CLIENT_LINK,a1.C_TAX_ID,a1.C_OP_DATE,a1.C_TERM_DATE, 
a1.C_MARKET_VALUE, a1.C_MAX_ACTIVITY_DATE,a1.C_CHANGE_OF_ADDR,a1.SUPPRESSION_DATE,a1.DocumentType,a1.CLIENT_ID,a1.C_ADD_COUNTRY,a1.ADDR_NAME_FIRST_A,
a1.ADDR_NAME_MIDDLE_A,a1.ADDR_NAME_LAST_A,a1.ADDR_NAME_SUFFIX_A,a1.ADDR_STREET_NUMBERADDRESS_A,a1.ADDR_CITY_A,
a1.ADDR_STATE_A,a1.ADDR_ZIP5ZIPCODE_A,a1.ADDR_TYPE_A,a1.ADDR_CONFIDENCE_A,a1.PH_PHONE_A_1,a1.PH_LISTING_NAME_A_1, 
a1.PH_PHONE_TYPE_A_1,a1.PH_PHONE_A_2,a1.PH_LISTING_NAME_A_2,a1.PH_PHONE_TYPE_A_2,a1.PH_PHONE_A_3,a1.PH_LISTING_NAME_A_3,
a1.PH_PHONE_TYPE_A_3 
FROM dbo.tblPWMMatchByDatesMod a1
WHERE COALESCE(DEC06,JAN07,FEB07,MAR07,APR07,MAY07,JUN07,JUL07,AUG07,SEP07,
OCT07,NOV07,DEC07,JAN08,FEB08,MAR08,APR08,MAY08,JUN08,JUL08,AUG08,SEP08,OCT08,NOV08,DEC08,
JAN09,FEB09,MAR09,APR09,MAY09,JUN09,JUL09,AUG09,SEP09,OCT09,NOV09,DEC09,JAN10,FEB10,MAR10,
APR10,MAY10,JUN10,JUL10,AUG10,SEP10,OCT10,NOV10,DEC10,JAN11,FEB11,MAR11,
APR11,MAY11,JUN11,JUL11,AUG11,SEP11,OCT11,NOV11,DEC11) =
 
(SELECT max(COALESCE(DEC06,JAN07,FEB07,MAR07,APR07,MAY07,JUN07,JUL07,AUG07,SEP07,
OCT07,NOV07,DEC07,JAN08,FEB08,MAR08,APR08,MAY08,JUN08,JUL08,AUG08,SEP08,OCT08,NOV08,DEC08,
JAN09,FEB09,MAR09,APR09,MAY09,JUN09,JUL09,AUG09,SEP09,OCT09,NOV09,DEC09,JAN10,FEB10,MAR10,
APR10,MAY10,JUN10,JUL10,AUG10,SEP10,OCT10,NOV10,DEC10,JAN11,FEB11,MAR11,
APR11,MAY11,JUN11,JUL11,AUG11,SEP11,OCT11,NOV11,DEC11))
 FROM dbo.tblPWMMatchByDatesMod a2
 WHERE a1.ACCOUNT = a2.ACCOUNT
 GROUP BY a2.account)) v1
 
 ON t1.account = v1.account
 GROUP BY t1.account, v1.Mailroom_Address1, v1.Mailroom_Address2, v1.Mailroom_Address3, v1.Mailroom_Address4, v1.Mailroom_Address5,
 t1.C_TAPS_ACCOUNT, t1.C_LNAME, t1.C_NAME_ADDR_TYPE, t1.C_ADDRESS1, t1.C_ADDRESS2, t1.C_ADDRESS3, t1.C_ADDRESS4, t1.CITY, t1.STATE,
 t1.ZIP, t1.Country, t1.B_BNAME, t1.B_WHO, t1.B_BROKER_ID, t1.C_ON_CLIENT_LINK, t1.C_TAX_ID, t1.C_OP_DATE, t1.C_TERM_DATE, t1.C_MARKET_VALUE,
 t1.C_MAX_ACTIVITY_DATE, t1.C_CHANGE_OF_ADDR, t1.SUPPRESSION_DATE, t1.DocumentType, t1.CLIENT_ID, t1.C_ADD_COUNTRY, t1.ADDR_NAME_FIRST_A,
 t1.ADDR_NAME_MIDDLE_A, t1.ADDR_NAME_LAST_A, t1.ADDR_NAME_SUFFIX_A, t1.ADDR_STREET_NUMBERADDRESS_A, t1.ADDR_CITY_A,
 t1.ADDR_STATE_A, t1.ADDR_ZIP5ZIPCODE_A, t1.ADDR_TYPE_A, t1.ADDR_CONFIDENCE_A, t1.PH_PHONE_A_1, t1.PH_LISTING_NAME_A_1,
 t1.PH_PHONE_TYPE_A_1, t1.PH_PHONE_A_2, t1.PH_LISTING_NAME_A_2, t1.PH_PHONE_TYPE_A_2, t1.PH_PHONE_A_3, t1.PH_LISTING_NAME_A_3,
 t1.PH_PHONE_TYPE_A_3

Open in new window

0
Comment
Question by:zimmer9
  • 2
  • 2
4 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35097074
so you have multiple mailroom details per account?

do you really need to do the max function on all the monthyear data... or is that just an attempt to
remove duplication?

is there any particular column(s) which would be the most sensible mailroom data (e.g. the latest)
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 35097377
try this
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[procPWMUniqueMod]
AS

If  Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'dbo.tblPWMMatchByDatesCMod' AND TYPE = 'U')
DELETE FROM dbo.tblPWMMatchByDatesCMod

INSERT INTO dbo.tblPWMMatchByDatesCMod
Select DEC06,JAN07,FEB07,MAR07,APR07,MAY07,JUN07,JUL07,AUG07,SEP07,
                    OCT07,NOV07,DEC07,JAN08,FEB08,MAR08,APR08,MAY08,JUN08,JUL08,AUG08,SEP08,OCT08,NOV08,DEC08,
                  JAN09,FEB09,MAR09,APR09,MAY09,JUN09,JUL09,AUG09,SEP09,OCT09,NOV09,DEC09,JAN10,FEB10,MAR10,
                  APR10,MAY10,JUN10,JUL10,AUG10,SEP10,OCT10,NOV10,DEC10,JAN11,FEB11,MAR11,
                   APR11,MAY11,JUN11,JUL11,AUG11,SEP11,OCT11,NOV11,DEC11
,Mailroom_Address1,Mailroom_Address2,Mailroom_Address3,Mailroom_Address4,
Mailroom_Address5
,C_TAPS_ACCOUNT,C_LNAME,C_NAME_ADDR_TYPE,C_ADDRESS1,C_ADDRESS2,C_ADDRESS3,C_ADDRESS4,CITY,
STATE,ZIP,Country,C_ADD_COUNTRY,B_BNAME,B_WHO,B_BROKER_ID,C_ON_CLIENT_LINK
,C_TAX_ID,C_OP_DATE,C_TERM_DATE,C_MARKET_VALUE,
C_MAX_ACTIVITY_DATE,C_CHANGE_OF_ADDR,SUPPRESSION_DATE,DocumentType,CLIENT_ID,ADDR_NAME_FIRST_A,
ADDR_NAME_MIDDLE_A,ADDR_NAME_LAST_A,ADDR_NAME_SUFFIX_A,ADDR_STREET_NUMBERADDRESS_A
,ADDR_CITY_A,ADDR_STATE_A,
ADDR_ZIP5ZIPCODE_A,ADDR_TYPE_A,ADDR_CONFIDENCE_A,PH_PHONE_A_1,PH_LISTING_NAME_A_1,
PH_PHONE_TYPE_A_1,PH_PHONE_A_2,PH_LISTING_NAME_A_2,PH_PHONE_TYPE_A_2,PH_PHONE_A_3
,PH_LISTING_NAME_A_3,
PH_PHONE_TYPE_A_3 

  from (select x.* 
              ,row_number() over (partition by account order by c_op_date desc) as rn
         from (
SELECT t1.ACCOUNT,max(t1.DEC06) as DEC06, max(t1.JAN07) as JAN07,max(t1.FEB07) as FEB07,max(t1.MAR07) as MAR07,
max(t1.APR07) as APR07,max(t1.MAY07) as MAY07,max(t1.JUN07) as JUN07,max(t1.JUL07) as JUL07,max(t1.AUG07) as AUG07,max(t1.SEP07) as SEP07,
max(t1.OCT07) as OCT07,max(t1.NOV07) as NOV07,max(t1.DEC07) as DEC07,max(t1.JAN08) as JAN08,max(t1.FEB08) as FEB08,max(t1.MAR08) as MAR08,
max(t1.APR08) as APR08,max(t1.MAY08) as MAY08,max(t1.JUN08) as JUN08,max(t1.JUL08) as JUL08,max(t1.AUG08) as AUG08,max(t1.SEP08) as SEP08,
max(t1.OCT08) as OCT08,max(t1.NOV08) as NOV08,max(t1.DEC08) as DEC08,max(t1.JAN09) as JAN09,max(t1.FEB09) as FEB09,max(t1.MAR09) as MAR09,
max(t1.APR09) as APR09,max(t1.MAY09) as MAY09,max(t1.JUN09) as JUN09,max(t1.JUL09) as JUL09,max(t1.AUG09) as AUG09,max(t1.SEP09) as SEP09,
max(t1.OCT09) as OCT09,max(t1.NOV09) as NOV09,max(t1.DEC09) as DEC09,max(t1.JAN10) as JAN10,max(t1.FEB10) as FEB10,max(t1.MAR10) as MAR10,
max(t1.APR10) as APR10,max(t1.MAY10) as MAY10,max(t1.JUN10) as JUN10,max(t1.JUL10) as JUL10,max(t1.AUG10) as AUG10,max(t1.SEP10) as SEP10,
max(t1.OCT10) as OCT10,max(t1.NOV10) as NOV10,max(t1.DEC10) as DEC10,max(t1.JAN11) as JAN11,max(t1.FEB11) as FEB11,max(t1.MAR11) as MAR11,
max(t1.APR11) as APR11,max(t1.MAY11) as MAY11,max(t1.JUN11) as JUN11,max(t1.JUL11) as JUL11,max(t1.AUG11) as AUG11,max(t1.SEP11) as SEP11,
max(t1.OCT11) as OCT11,max(t1.NOV11) as NOV11,max(t1.DEC11) as DEC11,
v1.Mailroom_Address1,v1.Mailroom_Address2,v1.Mailroom_Address3,v1.Mailroom_Address4,
v1.Mailroom_Address5
,t1.C_TAPS_ACCOUNT,t1.C_LNAME,t1.C_NAME_ADDR_TYPE,t1.C_ADDRESS1,t1.C_ADDRESS2,t1.C_ADDRESS3,t1.C_ADDRESS4,t1.CITY,
t1.STATE,t1.ZIP,t1.Country,t1.C_ADD_COUNTRY,t1.B_BNAME,t1.B_WHO,t1.B_BROKER_ID,t1.C_ON_CLIENT_LINK
,t1.C_TAX_ID,t1.C_OP_DATE,t1.C_TERM_DATE,t1.C_MARKET_VALUE,
t1.C_MAX_ACTIVITY_DATE,t1.C_CHANGE_OF_ADDR,t1.SUPPRESSION_DATE,t1.DocumentType,t1.CLIENT_ID,t1.ADDR_NAME_FIRST_A,
t1.ADDR_NAME_MIDDLE_A,t1.ADDR_NAME_LAST_A,t1.ADDR_NAME_SUFFIX_A,t1.ADDR_STREET_NUMBERADDRESS_A
,t1.ADDR_CITY_A,t1.ADDR_STATE_A,
t1.ADDR_ZIP5ZIPCODE_A,t1.ADDR_TYPE_A,t1.ADDR_CONFIDENCE_A,t1.PH_PHONE_A_1,t1.PH_LISTING_NAME_A_1,
t1.PH_PHONE_TYPE_A_1,t1.PH_PHONE_A_2,t1.PH_LISTING_NAME_A_2,t1.PH_PHONE_TYPE_A_2,t1.PH_PHONE_A_3
,t1.PH_LISTING_NAME_A_3,
t1.PH_PHONE_TYPE_A_3 

FROM dbo.tblPWMMatchByDatesMod 
t1 INNER JOIN (SELECT a1.account,a1.Mailroom_Address1,a1.Mailroom_Address2,a1.Mailroom_Address3
                    ,a1.Mailroom_Address4,a1.Mailroom_Address5
                FROM dbo.tblPWMMatchByDatesMod a1
               WHERE COALESCE(DEC06,JAN07,FEB07,MAR07,APR07,MAY07,JUN07,JUL07,AUG07,SEP07,
                    OCT07,NOV07,DEC07,JAN08,FEB08,MAR08,APR08,MAY08,JUN08,JUL08,AUG08,SEP08,OCT08,NOV08,DEC08,
                     JAN09,FEB09,MAR09,APR09,MAY09,JUN09,JUL09,AUG09,SEP09,OCT09,NOV09,DEC09,JAN10,FEB10,MAR10,
                     APR10,MAY10,JUN10,JUL10,AUG10,SEP10,OCT10,NOV10,DEC10,JAN11,FEB11,MAR11,
                     APR11,MAY11,JUN11,JUL11,AUG11,SEP11,OCT11,NOV11,DEC11) =
 
               (SELECT max(COALESCE(DEC06,JAN07,FEB07,MAR07,APR07,MAY07,JUN07,JUL07,AUG07,SEP07,
                    OCT07,NOV07,DEC07,JAN08,FEB08,MAR08,APR08,MAY08,JUN08,JUL08,AUG08,SEP08,OCT08,NOV08,DEC08,
                  JAN09,FEB09,MAR09,APR09,MAY09,JUN09,JUL09,AUG09,SEP09,OCT09,NOV09,DEC09,JAN10,FEB10,MAR10,
                  APR10,MAY10,JUN10,JUL10,AUG10,SEP10,OCT10,NOV10,DEC10,JAN11,FEB11,MAR11,
                   APR11,MAY11,JUN11,JUL11,AUG11,SEP11,OCT11,NOV11,DEC11))
                 FROM dbo.tblPWMMatchByDatesMod a2
                WHERE a1.ACCOUNT = a2.ACCOUNT
                )) v1
 
 ON t1.account = v1.account
 GROUP BY t1.account, v1.Mailroom_Address1, v1.Mailroom_Address2, v1.Mailroom_Address3, v1.Mailroom_Address4, v1.Mailroom_Address5,
 t1.C_TAPS_ACCOUNT, t1.C_LNAME, t1.C_NAME_ADDR_TYPE, t1.C_ADDRESS1, t1.C_ADDRESS2, t1.C_ADDRESS3, t1.C_ADDRESS4, t1.CITY, t1.STATE,
 t1.ZIP, t1.Country, t1.B_BNAME, t1.B_WHO, t1.B_BROKER_ID, t1.C_ON_CLIENT_LINK, t1.C_TAX_ID, t1.C_OP_DATE, t1.C_TERM_DATE, t1.C_MARKET_VALUE,
 t1.C_MAX_ACTIVITY_DATE, t1.C_CHANGE_OF_ADDR, t1.SUPPRESSION_DATE, t1.DocumentType, t1.CLIENT_ID, t1.C_ADD_COUNTRY, t1.ADDR_NAME_FIRST_A,
 t1.ADDR_NAME_MIDDLE_A, t1.ADDR_NAME_LAST_A, t1.ADDR_NAME_SUFFIX_A, t1.ADDR_STREET_NUMBERADDRESS_A, t1.ADDR_CITY_A,
 t1.ADDR_STATE_A, t1.ADDR_ZIP5ZIPCODE_A, t1.ADDR_TYPE_A, t1.ADDR_CONFIDENCE_A, t1.PH_PHONE_A_1, t1.PH_LISTING_NAME_A_1,
 t1.PH_PHONE_TYPE_A_1, t1.PH_PHONE_A_2, t1.PH_LISTING_NAME_A_2, t1.PH_PHONE_TYPE_A_2, t1.PH_PHONE_A_3, t1.PH_LISTING_NAME_A_3,
 t1.PH_PHONE_TYPE_A_3
) as x
where rn=1

Open in new window

0
 

Author Comment

by:zimmer9
ID: 35099200
I get an error as follows:

Msg 102, Level 15, State 1, Procedure procPWMUniquenessMod, Line 83
Incorrect syntax near '1'.

0
 

Author Comment

by:zimmer9
ID: 35099286
By the way, I renamed the stored procedure to procPWMUniquenessMod.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now