vlookup in range

Question

Post reply

vlookup in range

Shree23

SSC-Addicted

Points: 497
More actions
April 24, 2023 at 1:23 pm

#4178629

create table table11( col1 char,col2 int,col3 int)
Insert-------------------------------------------------------------
insert into table11 values(1, 2,175)

insert into table11 values(2, 4,111)

insert into table11 values(3, 6,202)
--------------------------create table------------------------
create table table11( col1 char,col2 int,col3 int)
Insert-------------------------------------------------------------
insert into table11 values(174, 2,187)
insert into table11 values(176, 4,188)
insert into table11 values(111, 6,111)
insert into table11 values(200, 8,200)
Expected output----------------------------------------------------
(1, 2,175,187)

(2, 4,111,111)

(3, 6,202,200)
condition any value to above 200 will be 200 only
three simple condition: any value to above 200 will be 200 only if the value between 174 and 176 then round function will be there if it is above 0.5 percentage then .value will be heated that is 176 ... example if the value comes like 175.90 then it will go to 176 ... it is simply round or nearest value logic

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

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

drew.allen SSC Guru Points: 77004 More actions · Answer 1

It's not clear what you are trying to accomplish here. And based on best guesses there are several issues.

It looks like you want to match Table1.Col3 to Table11.Col1, but there is a type mismatch.
- Table1.Col3 is INT
- Table11.Col1 is CHAR
  - You also don't specify the length
Rounding does not apply to CHAR columns
The standard with rounding is to round up at 0.5
- It looks like you are rounding down.
- It's also 0.5 (50%) not 0.5 percentage(0.005)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Shree23 SSC-Addicted Points: 497 More actions · Answer 2

Shree23

SSC-Addicted

Points: 497

April 24, 2023 at 7:48 pm

#4178781

Please consider all as integer datatype

Steve Collins SSCrazy Points: 2184 More actions · Answer 3

No idea what's meant by the rounding. Presumably (to get the correct output) there's a '<=' inequality. To handle the range between 174 and 176 you could create a partial cross join by using OUTER APPLY which then filters the rows based on the 'col3' value in t11. Not pretty, or sargable, but returns the requested output

select t11.*, t22.col3
from #table11 t11
     outer apply (select v.n
                  from (values (174),(175),(176)) v(n)
                  where t11.col3 between 174 and 176
                        and v.n<=t11.col3) x(n)
     join #table22 t22 on isnull(x.n, iif(t11.col3>200, 200, t11.col3))=t22.col1;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Shree23 SSC-Addicted Points: 497 More actions · Answer 4

it is hard coded solution ... the values can be vary