Compare and update sql server query

I have a table with following fields

updateto hb AD AdDate Hub HubDate

1 5 8/4/2016 null null
6 null null 8 8/5/2016
7 7 8/1/2016 5 8/4/2016
11 11 8/3/2016 11 5/4/2016

so we will need to update the "updateto" field following way

for row 1) we compare AD and hub and since hub is null updateto = AD
row 2 ) we compare AD with hub and since AD is null updateto = hub
row 3 ) we compare AD and hub with hb . since AD matches hb updateto = AD
row 4 ) we compare AD and hub with hb and since they all three are same we compare addate with hubdate and since hubdate is higher updateto = hub

there are more then 4 rows in the table. I have just given an example over here.

how do i write a sql statement to make this happen.

Microsoft SQL Server 2008Microsoft SQL Server

Last Comment

Vitor Montalvão

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

Jim Horn

8/4/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Zberteoc

8/4/2016

Try this:

--/*
if object_id('tempdb..#temp') is not null
    drop table #temp
create table #temp(updateto varchar(100), hb int, AD int, AddDate date, Hub int, HubDate date)
insert into #temp(hb, AD, AddDate, Hub, HubDate)
select * from
    (values
	   (1,        5,         '8/4/2016',          null,    null),
	   (6,        null,     null,                   8,         '8/5/2016'),
	   (7,        7,          '8/1/2016',         5,         '8/4/2016'),
	   (11,      11,        '8/3/2016',         11,       '5/4/2016')
    ) a(hb, AD, AdDate, Hub, HubDate)
--*/

select * from #temp

update #temp set
    updateto=
	   case 
		  when hub is null then AD
		  when AD is null then Hub
		  when AD=Hub then AD
		  when AD=Hub and Hub=hb and HubDate>AddDate then Hub 
		  else AD
    end


select * from #temp

Open in new window

Olaf Doschke

8/4/2016

In regard to the rules theres just one case missing, what if AD = hub AND hub = hb AND addate >= hubdate, I'd say that'll mean AD again. So

WHEN AD = hub AND hub = hb AND addate < hubdate THEN hub ELSE AD END

Bye, Olaf.

Zberteoc

8/4/2016

@Olaf,

This has nothing to do with the question.

Every time I read that "Bye, Olaf" ending formula it feels so definitive, like you will never come back, but still you do. :o)))

Talk to you later, Zberteoc. :o)

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Vitor Montalvão

8/5/2016

What's happening when AD and HUB are both NULL?