September 12, 2012 at 3:08 pm
Hello Experts,
I have a table with a column called Z_Status: the values of this column are DUP and Survivor, which means one of the record is Duplicate and another is survivor.
Now i have to update last Column called New_Account from Column C (EV870_ACCT_CODE), the New_Account Column should only be updated by the Max(EV870_ACCT_CODE) when grouping the information by Key2 Column.
I have shown the output and highlited the result in Green background (please check the attachment).
Apologize if i have not explained it well.
Waiting for the suggestions
Cheers
Ish
September 13, 2012 at 2:21 am
please help
September 13, 2012 at 2:58 am
You will be better off posting data in a consumable format in order to get help quickly, please see the second link in my signature for help on this
I have done this for you below and also given 1 solution, others may chip in now that they can easily recreate the problem on their setups.
DECLARE @table TABLE
(
Z_Status VARCHAR(10),
Z_Linked_Account INT,
EV870_ACCT_CODE INT,
EV870_NAME VARCHAR(500),
Fuzzy_name VARCHAR(500),
EV870_Company_Name VARCHAR(500),
EV870_Email_Address VARCHAR(500),
Country CHAR(3),
Key1 VARCHAR(500),
Key2 VARCHAR(500),
New_Account INT
)
INSERT INTO @table VALUES
('SURVIVOR',27092,27093,'Abes, Jake','ABESJAKE','Det Norske Veritas','jake.abes@dnv.com','CAN','ABESJAKE jake.abes@dnv.com CAN','ABESJAKE jake.abes@dnv.com',null),
('SURVIVOR',12350,13972,'Abid, Ridha','ABIDRIDHA','OPSENS','ridha.abid@opsens.com','CAN','ABIDRIDHA ridha.abid@opsens.com CAN','ABIDRIDHA ridha.abid@opsens.com',null),
('SURVIVOR',12394,32648,'Abraham, Jennifer','ABRAHAMJENNIFER','Honeywell Process Solutions','jennifer.abraham@honeywell.com','USA','ABRAHAMJENNIFER jennifer.abraham@honeywell.com USA','ABRAHAMJENNIFER jennifer.abraham@honeywell.com',null),
('SURVIVOR',13385,16410,'Abraham, Kurt','ABRAHAMKURT','Gulf Publishing Company','kurt.abraham@worldoil.com','USA','ABRAHAMKURT kurt.abraham@worldoil.com USA','ABRAHAMKURT kurt.abraham@worldoil.com',null),
('SURVIVOR',11126,21300,'Abrams, Adriane','ABRAMSADRIANE','VaporTech Energy Services Inc.','aabrams@vaportechinc,com','CAN','ABRAMSADRIANE aabrams@vaportechinc,com CAN','ABRAMSADRIANE aabrams@vaportechinc,com',null),
('SURVIVOR',10996,17945,'Accadia, Mike','ACCADIAMIKE','Phoenix Contact Ltd.','maccadia@phoenixcontact.ca','CAN','ACCADIAMIKE maccadia@phoenixcontact.ca CAN','ACCADIAMIKE maccadia@phoenixcontact.ca',null),
('DUP',10996,14398,'Accadia, Mike','ACCADIAMIKE','Phoenix Contact Ltd.','maccadia@phoenixcontact.ca','CAN','ACCADIAMIKE maccadia@phoenixcontact.ca CAN','ACCADIAMIKE maccadia@phoenixcontact.ca',null),
('SURVIVOR',10298,32526,'Accounting, Emily','ACCOUNTINGEMILY','CanTorque Inc.','accounting@cantorque.com','CAN','ACCOUNTINGEMILY accounting@cantorque.com CAN','ACCOUNTINGEMILY accounting@cantorque.com',null),
('SURVIVOR',26870,26872,'Acc Payable, Eileen','ACCPAYABLEEILEEN','Power Plant Supply Co.',null,'CAN','ACCPAYABLEEILEEN CAN','ACCPAYABLEEILEEN',null),
('SURVIVOR',30473,30480,'Acevedo, Enrique','ACEVEDOENRIQUE','Correcol S.A.','eacevedo@correcol.com','COL','ACEVEDOENRIQUE eacevedo@correcol.com COL','ACEVEDOENRIQUE eacevedo@correcol.com',null),
('SURVIVOR',23081,23157,'Acheampong, Nicole','ACHEAMPONGNICOLE','Aerotek','nacheamp@aerotek.com','USA','ACHEAMPONGNICOLE nacheamp@aerotek.com USA','ACHEAMPONGNICOLE nacheamp@aerotek.com',null),
('SURVIVOR',10129,23147,'Acheson, Harry','ACHESONHARRY','TISI Canada Inc.','HAcheson@teamindustrialservices.com','CAN','ACHESONHARRY HAcheson@teamindustrialservices.com CAN','ACHESONHARRY HAcheson@teamindustrialservices.com',null),
('DUP',13285,13954,'Address, Mailing','ADDRESSMAILING','PETROBRAS',NULL,'BRZ','ADDRESSMAILING BRZ','ADDRESSMAILING',null),
('SURVIVOR',10919,15507,'Address, Mailing','ADDRESSMAILING','PETROBRAS',NULL,'BRZ','ADDRESSMAILING BRZ','ADDRESSMAILING',null),
('SURVIVOR',34530,34531,'Abdulla, Feroz','ABDULLAFEROZ','Metalcraft Technology Inc.','feroz@metalcraft.ca','CAN','ABDULLAFEROZ feroz@metalcraft.ca CAN','ABDULLAFEROZ feroz@metalcraft.ca',null),
('SURVIVOR',12027,18555,'Abel, Tim','ABELTIM','Roevin Technical People','tabel@roevin.ca','CAN','ABELTIM tabel@roevin.ca CAN','ABELTIM tabel@roevin.ca',null),
('DUP',11895,15083,'Ahner, Julie','AHNERJULIE','Tyco Thermal Controls','jahner@tycothermal.com','USA','AHNERJULIE jahner@tycothermal.com USA','AHNERJULIE jahner@tycothermal.com',null),
('SURVIVOR',10539,15536,'Ahner, Julie','AHNERJULIE','Tyco Thermal Controls','jahner@tycothermal.com','USA','AHNERJULIE jahner@tycothermal.com USA','AHNERJULIE jahner@tycothermal.com',null)
select * from @table
update
t
set
New_Account = maxacctno
from
@table t
inner join
(
select
MAX(ev870_acct_code) AS maxacctno,
ev870_name
from
@table
group by
ev870_name
) as Dev1
on
t.EV870_NAME = dev1.EV870_NAME
select * from @table
September 13, 2012 at 5:12 am
Hello Anthony,
It works perfectly, thank you so much
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply