?
Solved

Compare and update sql server query

Posted on 2016-08-04
5
Medium Priority
?
78 Views
Last Modified: 2016-08-09
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.
0
Comment
Question by:PratikShah111
5 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 41742573
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41742575
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
 
LVL 31

Expert Comment

by:Olaf Doschke
ID: 41742754
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41742778
@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
 
LVL 54

Expert Comment

by:Vitor Montalvão
ID: 41743997
What's happening when AD and HUB are both NULL?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.

Join & Write a Comment

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

589 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