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.
PratikShah111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl..
UPDATE YourTable
SET updateto = CASE 
   WHEN hub IS NULL THEN AD
   WHEN AD IS NULL THEN hub
   WHEN AD = hub AND hub <> hb THEN AD
   WHEN AD = hub AND hub = hb AND addate < hubdate THEN hub END

Open in new window

For more info on CASE blocks check out SQL Server CASE Solutions
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZberteocCommented:
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

0
Olaf DoschkeSoftware DeveloperCommented:
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.
0
ZberteocCommented:
@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)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
What's happening when AD and HUB are both NULL?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.