Solved

Merging multiple accounts into recent one using sp

Posted on 2014-09-15
2
90 Views
Last Modified: 2014-09-17
Dear,

I have 2 tables, one is "custommers" which is allowed to create multiple accounts with same email address. "contactid" is auto incremental key. When Orders are placed they are recorded in "orders" table where "orderid" is unique incremental key. In orders table field ocustomerid = custommers.contactid and it is filled based on account user logged in while placing an order.

I need store procedure which can do following:

1) In "Customers" table merge accounts if created more than once on same email address into one (using recent contactid) and accumulate pointstotal and pointsremaining from other accounts into the merged account. See example below:

Present situation:

contactid,email,pointstotal,pointsremaining
30424,markburriss@me.com,28,28
17863,theluderfamily@yahoo.com,85,85
23701,theluderfamily@yahoo.com,68,68
27415,theluderfamily@yahoo.com,NULL,NULL
30030,theluderfamily@yahoo.com,125,20
10231,theluderfamily@yahoo.com,387,387

Desired results:

contactid,email,pointstotal,pointsremaining
30424,markburriss@me.com,28,28
30030,theluderfamily@yahoo.com,665,560

2) Updating ocustomerid in "orders" table with contactid of the merged account (recent contactid) where customers.email=orders.oemail

Present Situation:

orderid,ocustomerid,oemail
64872,30424,markburriss@me.com
36594,17863,theluderfamily@yahoo.com
63196,10231,theluderfamily@yahoo.com
63963,30030,theluderfamily@yahoo.com
24302,10231,theluderfamily@yahoo.com
56593,10231,theluderfamily@yahoo.com
56767,10231,theluderfamily@yahoo.com
39187,17863,theluderfamily@yahoo.com
39195,17863,theluderfamily@yahoo.com
48667,23701,theluderfamily@yahoo.com
18659,10231,theluderfamily@yahoo.com
57409,27415,theluderfamily@yahoo.com
58106,10231,theluderfamily@yahoo.com
58142,10231,theluderfamily@yahoo.com
58662,10231,theluderfamily@yahoo.com

Desired result:

orderid,ocustomerid,oemail
64872,30424,markburriss@me.com
36594,30030,theluderfamily@yahoo.com
63196,30030,theluderfamily@yahoo.com
63963,30030,theluderfamily@yahoo.com
24302,30030,theluderfamily@yahoo.com
56593,30030,theluderfamily@yahoo.com
56767,30030,theluderfamily@yahoo.com
39187,30030,theluderfamily@yahoo.com
39195,30030,theluderfamily@yahoo.com
48667,30030,theluderfamily@yahoo.com
18659,30030,theluderfamily@yahoo.com
57409,30030,theluderfamily@yahoo.com
58106,30030,theluderfamily@yahoo.com
58142,30030,theluderfamily@yahoo.com
58662,30030,theluderfamily@yahoo.com

As you can see if there is single account in customers table, it will remain untouched. Only multiple accounts created on same email address should be merged.

Please provide workable solution. I am using MS SQL 2005.

Below is mentioned sql statements to create above tables and fill with present data mentioned above.

Thanks.

Nick

SQL STATEMENTS
==============

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)
;



Insert into dbo.customers values(30424, 'markburriss@me.com', 28, 28);
Insert into dbo.customers values(17863, 'theluderfamily@yahoo.com', 85, 85);
Insert into dbo.customers values(23701, 'theluderfamily@yahoo.com', 68, 68);
Insert into dbo.customers values(27415, 'theluderfamily@yahoo.com', NULL, NULL);
Insert into dbo.customers values(30030, 'theluderfamily@yahoo.com', 125, 125);
Insert into dbo.customers values(10231, 'theluderfamily@yahoo.com', 387, 387);


Create Table dbo.orders(
orderid                       int                 NOT NULL,
ocustomerid                   int,
odate                         datetime,
orderamount                   money,
ofirstname                    VARCHAR(100),
olastname                     VARCHAR(100),
oemail                        VARCHAR(100),
oaddress                      VARCHAR(200),
ocity                         VARCHAR(100),
opostcode                     VARCHAR(15),
ostate                        VARCHAR(100),
ocountry                      VARCHAR(50),
ophone                        VARCHAR(30),
ofax                          VARCHAR(30),
ocompany                      VARCHAR(255),
ocardtype                     VARCHAR(50),
ocardno                       VARCHAR(100),
ocardname                     VARCHAR(200),
ocardexpires                  VARCHAR(10),
ocardaddress                  VARCHAR(150),
oprocessed                    bit                 NOT NULL,
ocomment                      VARCHAR(255),
otax                          money,
opromisedshipdate             datetime,
oshippeddate                  datetime,
oshipmethod                   int,
oshipcost                     money,
oshipname                     VARCHAR(100),
oshipcompany                  VARCHAR(200),
oshipemail                    VARCHAR(100),
oshipmethodtype               VARCHAR(100),
oshipaddress                  VARCHAR(255),
oshiptown                     VARCHAR(50),
oshipzip                      VARCHAR(20),
oshipstate                    VARCHAR(50),
oshipcountry                  VARCHAR(50),
opaymethod                    int,
other1                        VARCHAR(50),
other2                        VARCHAR(50),
otime                         datetime,
oauthorization                VARCHAR(255),
oerrors                       VARCHAR(255),
odiscount                     float,
ostatus                       VARCHAR(255),
oaffid                        int,
odualtotal                    real,
odualtaxes                    real,
odualshipping                 real,
odualdiscount                 real,
ohandling                     real,
odualhandling                 real,
coupon                        VARCHAR(100),
coupondiscount                money,
coupondiscountdual            real,
giftcertificate               VARCHAR(100),
giftamountused                money,
giftamountuseddual            real,
canceled                      bit,
ipaddress                     VARCHAR(100),
hackeryesno                   bit,
customercancel                bit,
opoints                       int,
vatnumber                     VARCHAR(50),
commission                    money,
opending                      VARCHAR(100),
shipmessage                   VARCHAR(255),
hearaboutus                   VARCHAR(100),
oaddress2                     VARCHAR(100),
oshipaddress2                 VARCHAR(100),
deliverydate                  VARCHAR(50),
deliverytime                  VARCHAR(50),
producttotal                  money,
oinsurancemethodtype          VARCHAR(100),
oinsurancecost                VARCHAR(100),
oinsurancemethod              int,
odualinsurance                money,
upstrackno                    VARCHAR(50),
opst                          money,
odualpst                      money,
otermsagreed                  VARCHAR(10),
affdatepaid                   datetime,
affpaid                       bit,
ocurrency                     VARCHAR(50),
ocurrencyrate                 real,
cus_height                    CHAR(20),
cus_chest                     CHAR(20),
cus_waist                     CHAR(20),
cus_hat                       CHAR(20),
cus_shoe                      CHAR(20),
cus_declined                  bit,
cus_feedback                  bit,
actual_order                  datetime)
;


Insert into dbo.orders values(64872, 30424, 'markburriss@me.com');
Insert into dbo.orders values(36594, 17863, 'theluderfamily@yahoo.com');
Insert into dbo.orders values(63196, 10231, 'theluderfamily@yahoo.com');
Insert into dbo.orders values(63963, 30030, 'theluderfamily@yahoo.com');
Insert into dbo.orders values(24302, 10231, 'theluderfamily@yahoo.com');
Insert into dbo.orders values(56593, 10231, 'theluderfamily@yahoo.com');
Insert into dbo.orders values(56767, 10231, 'theluderfamily@yahoo.com');
Insert into dbo.orders values(39187, 17863, 'theluderfamily@yahoo.com');
Insert into dbo.orders values(39195, 17863, 'theluderfamily@yahoo.com');
Insert into dbo.orders values(48667, 23701, 'theluderfamily@yahoo.com');
Insert into dbo.orders values(18659, 10231, 'theluderfamily@yahoo.com');
Insert into dbo.orders values(57409, 27415, 'theluderfamily@yahoo.com');
Insert into dbo.orders values(58106, 10231, 'theluderfamily@yahoo.com');
Insert into dbo.orders values(58142, 10231, 'theluderfamily@yahoo.com');
Insert into dbo.orders values(58662, 10231, 'theluderfamily@yahoo.com');

Open in new window

0
Comment
Question by:NickHoward
2 Comments
 
LVL 1

Expert Comment

by:Sleepydog
ID: 40324318
Situation 1:

Select c.contactid,c.email,SUM (c.pointstotal) as pointstotalsum , SUM (c.pointsremaining) as pointsremainingsum FROM dbo.orders AS o
LEFT JOIN dbo.customers AS C
ON o.contactid =c.contactid 
WHERE c.email=o.oemail

Open in new window


Situation 2:
Select o.oderid,o.ocustomerid,o.oemail FROM dbo.orders AS o
LEFT JOIN dbo.customers AS C
ON o.ocustomerid = c.customerid 
WHERE c.email=o.oemail

Open in new window

0
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40324337
This small query (example) will do the trick:
select c.contactid, c.email, d.new_id, d.pointstotal, d.pointsremaining
INTO #temp
FROM dbo.customers c
INNER JOIN
(SELECT email, max(contactid) new_id, sum(pointstotal) pointstotal, sum(pointsremaining) pointsremaining
FROM dbo.customers group by email
having count(*) > 1) d ON c.email = d.email

update o set ocustomerid = t.new_id
FROM dbo.orders o INNER JOIN #temp t
ON o.ocustomerid = t.contactid

delete from dbo.customers
WHERE contactid in (select t.contactid FROM #temp t where t.new_id <> t.contactid)

update c set c.pointstotal = t.pointstotal, c.pointsremaining = t.pointsremaining
FROM dbo.customers c INNER JOIN #temp t
ON c.contactid = t.new_id

select * from dbo.customers;
select * from dbo.orders
drop table #temp

Open in new window

Basically, you select the duplicated records into a #temp table and use it for updating other table(s). When you updated all the related records and deleted the "old" customers you can drop the #temp table
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Backup & Restore 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.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

20 Experts available now in Live!

Get 1:1 Help Now