Solved

need sql statement or storeprocedure to merge  multiple registrations into one

Posted on 2014-09-14
11
158 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
  • 4
  • 4
  • 3
11 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 400 total points
Comment Utility
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
Comment Utility
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 65

Expert Comment

by:Jim Horn
Comment Utility
>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
 

Author Comment

by:NickHoward
Comment Utility
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 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 48

Expert Comment

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

Expert Comment

by:Jim Horn
Comment Utility
>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
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
I am using SQL 2005.
Nick
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now