?
Solved

need sql statement or storeprocedure to merge  multiple registrations into one

Posted on 2014-09-14
11
Medium Priority
?
174 Views
Last Modified: 2014-09-15
Dear,

I need help to delete multiple registration ROWS while only keep the last ONE (the latest by contactid) and while doing this I want to accumulates total points and remaing points from multiple registrations to be deleted into the last ONE (table: dbo.customers). The main condition to execute is if email exist more than once.

For an example (at present) one email has been used to create 5 registration records:

contactid	pointstotal	pointsremaining	email   ...   ....   ...  ...
17863	85	85	theluderfamily@yahoo.com   ...   ....   ...   ...
23701	68	20	theluderfamily@yahoo.com   ...   ....   ...   ...
27415	21	21	theluderfamily@yahoo.com   ...   ....   ...   ...
30030	125	125	theluderfamily@yahoo.com   ...   ....   ...   ...
10231	387	387	theluderfamily@yahoo.com   ...   ....   ...   ...

Open in new window

Result as:
contactid	pointstotal	pointsremaining	email  ...  ... ... .. .. 
30030	686	638	theluderfamily@yahoo.com ... ...  ...  ...  ..

Open in new window

Please provide me set of sql statements to execute when email is used more than once to create registrations.

Appreciate if store procedure is provided to accomplish above job.

Thanks in advance for your assistance.


Nick

Database table:

Create Table dbo.customers(
contactid                     int                 NOT NULL,
firstname                     VARCHAR(50),
lastname                      VARCHAR(50),
address                       VARCHAR(255),
city                          VARCHAR(100),
state                         VARCHAR(100),
postcode                      VARCHAR(20),
country                       VARCHAR(100),
company                       VARCHAR(255),
phone                         VARCHAR(50),
workphone                     VARCHAR(30),
mobilephone                   VARCHAR(30),
fax                           VARCHAR(30),
email                         VARCHAR(50),
contacttypeid                 int,
comments                      VARCHAR(255),
contactreason                 VARCHAR(20),
logincount                    int,
lastlogindate                 datetime,
website                       VARCHAR(200),
password                      VARCHAR(50),
discount                      float,
custother1                    VARCHAR(100),
accountno                     VARCHAR(50),
cardno                        VARCHAR(20),
cardexpires                   VARCHAR(10),
cardname                      VARCHAR(100),
cardaddress                   VARCHAR(200),
cardcvn                       VARCHAR(50),
maillist                      bit,
productgroup                  VARCHAR(100),
cookiequestion                bit,
pointstotal                   int,
pointsremaining               int,
vatnumber                     VARCHAR(50),
userid                        VARCHAR(50),
hearaboutus                   VARCHAR(100),
address2                      VARCHAR(100),
taxexempt                     VARCHAR(50),
authorized                    bit,
cus_height                    CHAR(20),
cus_chest                     CHAR(20),
cus_waist                     CHAR(20),
cus_hat                       CHAR(20),
cus_shoe                      CHAR(20),
date_created                  datetime)
;

Open in new window

0
Comment
Question by:NickHoward
[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
  • 4
  • 4
  • 3
11 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1200 total points
ID: 40321894
If by 'delete' you mean 'just show me...', then here's the query
SELECT email, SUM(pointstotal) as pointstotal_sum, sum(pointsremaining) as pointsremaining_sum, Max(Contactid) as Contactid
FROM your_table
GROUP BY email

Open in new window


If the goal is to make a SP out of it, and I'll ignor the table schema above, then here you go..
CREATE PROC name_me AS

-- Throw this all in a transaction so the entire SP succeeds or fails together

BEGIN TRY

   BEGIN TRAN tr

   -- Create a temp table to store the summarized values
   CREATE TABLE #tmp (email varchar(100), pointstotal_sum int, pointsremaining_sum int, contactid int) 

   -- Insert the summarized values into the temp table
   INSERT INTO #tmp (email, pointstotal_sum, pointsremaining_sum, contactid)
   SELECT email, SUM(pointstotal) as pointstotal_sum, sum(pointsremaining) as pointsremaining_sum, Max(Contactid) as Contactid
   FROM your_table
   GROUP BY email


   -- Delete everything from the tables
   DELETE FROM your_table

   -- Now insert the summarized values into the table
   INSERT INTO your_table (email, pointstotal_sum, pointsremaining_sum, contactid)
   SELECT email, pointstotal_sum, pointsremaining_sum, contactid
   FROM #tmp

   -- If code execution makes it here, good to go. 
   COMMIT TRAN tr

END TRY

BEGIN CATCH
   -- An error occured.  Kill the transaction 
   ROLLBACK TRAN tr
END CATCH

GO

Open in new window


Here's a couple of articles I cranked out that may help you:
SQL Server Delete Duplicate Rows Solutions
SQL Server GROUP BY Solutions
0
 

Author Comment

by:NickHoward
ID: 40321979
Hi Jim,

Thanks for your quick answer.

I liked the SP however I think you are deleting the "customers" table and then filling from temp table. Since field "Contactid" is incremental key and it is used in "orders" table, I am afraid if it would not possible to retain it.

I wish that last "Contactid" is updated while extras are deleted. Also "orders" table is updated with correct "Contactid" since there are entries with multiple contactids while email is same.

For instance (existing):

orderid    contactid  email
19210      23701       theluderfamily@yahoo.com
20122      30030       theluderfamily@yahoo.com
19333      27415       theluderfamily@yahoo.com

Become to:

orderid    contactid  email
19210      30030      theluderfamily@yahoo.com
20122      30030       theluderfamily@yahoo.com
19333      30030       theluderfamily@yahoo.com

Again here for "orders" table update option is preferred.

Hope you got my point and I would have workable solution soon.

Many thanks.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40321989
>Since field "Contactid" is incremental key and it is used in "orders" table, I am afraid if it would not possible to retain it.
>I wish that last "Contactid" is updated while extras are deleted.
>Also "orders" table is updated with correct "Contactid" since there are entries with multiple contactids while email is same.
It would greatly help if you could supply all relevant details in the initial question, as it sounds like this is now multiple questions.
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

Author Comment

by:NickHoward
ID: 40321997
Jim,

Sorry I missed it. I will create new question for updating orders table.

You can for the moment resolve original request.

Thanks..
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 300 total points
ID: 40322444
I was about to recommend MERGE here

BUT; I have just noticed http:#a40321979
which changes the question substantially pity it wasn't on the question itself.

The following was for the original question.
MERGE customers AS target
USING (
      SELECT
            *
      FROM (
            SELECT
                  contactid
                , email
                , ROW_NUMBER() OVER (PARTITION BY email ORDER BY contactid DESC) AS rn
                , SUM(pointstotal) OVER (PARTITION BY email)                     AS pointstotal
                , SUM(pointsremaining) OVER (PARTITION BY email)                 AS pointsremaining
            FROM customers
            ) AS s
      WHERE rn = 1
      ) AS source
            ON target.contactid = source.contactid
WHEN MATCHED
      THEN UPDATE SET
              target.pointstotal = source.pointstotal
            , target.pointsremaining = source.pointsremaining
WHEN NOT MATCHED BY SOURCE
      THEN DELETE
;
GO


select
*
from customers
GO

Open in new window

| CONTACTID | POINTSTOTAL | POINTSREMAINING |                    EMAIL |
|-----------|-------------|-----------------|--------------------------|
|     30030 |         686 |             638 | theluderfamily@yahoo.com |

Open in new window

see: http://sqlfiddle.com/#!3/e8f30/1
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40322446
I don't understand the requirement now. Could you specify all tables involved and provide sample data for each please.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40322911
>I don't understand the requirement now.
Barring any objection from Paul, I strongly suggest closing the question by splitting points, spending some time gathering all of your requirements and needed details, and asking it as a new question.
0
 

Author Comment

by:NickHoward
ID: 40324053
Hi Paul,

When I tried to execute the MERGE t-sql I got this error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'AS'.

Please advise.

Thanks.

t-sql
MERGE customers AS target
USING (
      SELECT
            *
      FROM (
            SELECT
                  contactid
                , email
                , ROW_NUMBER() OVER (PARTITION BY email ORDER BY contactid DESC) AS rn
                , SUM(pointstotal) OVER (PARTITION BY email)                     AS pointstotal
                , SUM(pointsremaining) OVER (PARTITION BY email)                 AS pointsremaining
            FROM customers
            ) AS s
      WHERE rn = 1
      ) AS source
            ON target.contactid = source.contactid
WHEN MATCHED
      THEN UPDATE SET
              target.pointstotal = source.pointstotal
            , target.pointsremaining = source.pointsremaining
WHEN NOT MATCHED BY SOURCE
      THEN DELETE
;
GO


select
*
from customers
GO

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40324397
what version of SQL Server are you using?
(MERGE is from 2008 up)

If you are on 2005 or less then you will have to use inserts and delete separately.
0
 

Author Comment

by:NickHoward
ID: 40324611
I am using SQL 2005.
Nick
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40324625
Thanks, I see you have another question open and that you specified the version (that's a very good thing to do)

Regrettably I now tend to ignore the version indicated by topic as there is only SQL Server 2005 and 2008 available as topics (sql 2012 and 2014 are available, some still cling to 2000) and so you wouldn't believe the number of times the topic does not align to the actual version. Sorry, you had it right and I took a gamble.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

771 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