Solved

need sql statement or storeprocedure to merge  multiple registrations into one

Posted on 2014-09-14
11
172 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 400 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 100 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

729 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