Compare and Round to nearest value from two tables

Question

Compare and Round to nearest value from two tables

rameshduraikannu

SSC Veteran

Points: 211
More actions
November 11, 2009 at 11:55 pm

#98336

Hai to all,
i had two table range1,range2
first table column value is
height=200
second table column value is
height=206
I want to compare two column value and round the value to nearest first table column value

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

rameshduraikannu (11/11/2009)
Hai to all,
i had two table range1,range2
first table column value is
height=200
second table column value is
height=206
I want to compare two column value and round the value to nearest first table column value

your requirement is not clear. But I will attempt to understand! you have a value (may be in a variable?) and you want to compare that value to these two column values and then may be round it?

---------------------------------------------------------------------------------

rameshduraikannu SSC Veteran Points: 211 More actions · Answer 2

hai Veteran

Thanks for u reply

compare two value

value1:100

value2:104

the value2 is nearest to value1 so

100(value1) compare 104 (value2) and round nearest value2 to value1

100(compare)104 ==> (nearest)100

i think so it must be understable...

Nabha SSCrazy Eights Points: 8534 More actions · Answer 3

rameshduraikannu (11/12/2009)
100(compare)104 ==> (nearest)100
i think so it must be understable...

Sorry, I could not understand. Can you provide some test data and table script and then the expected result?

When you say 'compare' , do you mean to say some comparision operators as listed here?

http://msdn.microsoft.com/en-us/library/ms188074.aspx

What do you mean by 'nearest'? if I compare two values above I can say that 100 is 'smaller' compared to 104. Just let us know your table structure, some sample data and what you are trying to achieve, we might help you with other options!

---------------------------------------------------------------------------------

rameshduraikannu SSC Veteran Points: 211 More actions · Answer 4

hai...

i have attached the example see that and let me know the solution

Nabha SSCrazy Eights Points: 8534 More actions · Answer 5

Does this help you?

Create table #user(location int, grade_value int)

INSERT INTO #admin VALUES(1,240)

INSERT INTO #admin VALUES(2,270)

INSERT INTO #admin VALUES(3,300)

INSERT INTO #user VALUES(1,246)

INSERT INTO #user VALUES(1,273)

INSERT INTO #user VALUES(2,301)

Select location, grade,nearest from (

Select u.location as location,u.grade_value as grade,

a.grade as nearest, row_number() OVER (PARTITION BY grade_value order by

ABS(grade_value-grade)) as row_num

from #admin a

CROSS JOIN #user u) t

where row_num = 1

---------------------------------------------------------------------------------