Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-10
4
Medium Priority
?
182 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

722 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