Solved

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

Posted on 2011-03-10
4
177 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
hit enter key to run macro 13 23
Help Required 2 29
DSN-LESS connection to MS Access database 6 29
Opening forms with an input box 5 15
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

786 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