VLOOKUP Equivalent in SQL

SQL Server Administration
1

Hi there

I have two tables, one with an ID and Amount (Table 1) and the Second with a Column called Variable with an entry called "Amount". This "Amount" has different values in the columns labelled 1,2,3,4 and 5 (Table 2).

How do I create a new column in a new Table (Table 3) with columns ID and Amount, that brings into the Amount column for each ID: a 1 if the Amount in Table 1 is found in a range of values that are in columns 1 and 2 (between the values in columns 1 and 2 in Table 2), that brings in a 2 if the Amount in Table 1 is found in a range of values that are in columns 2 and 3 (between the values in columns 2 and 3 in Table 2) , that brings in a 3 if the Amount in Table 1 is found in a range of values that are in columns 3 and 4 (between the values in columns 3 and 4 in Table 2), that brings in a 4 if the Amount in Table 1 is found in a range of values that are in columns 4 and 5 (between the values in columns 4 and 5 in Table 2) and brings in a 5 if the amount in Table 1 is greater than the value in Column 5 corresponding to the "amount" row in Table 2.

Any help will be much appreciated. Thank you

2

Welcome

Please help us help you by providing us sample data to use

please post it as proper SQL script DDL

create table tbl_AGE

detailing the data type of each column, cause we don't know

then add the sample data as follows DML

insert into tbl_AGE 
select 1, 1, 'Sam', 800

fill in the rest. help us help you

3 
SELECT t1.ID, t1.Amount, t2.Amount_VLOOKUP
FROM table1 t1
CROSS APPLY (
    SELECT CASE
        WHEN t1.Amount < t2.col1 THEN 0
        WHEN t1.Amount >= t2.col1 AND t1.Amount < t2.col2 THEN 1
        WHEN t1.Amount >= t2.col2 AND t1.Amount < t2.col3 THEN 2
        WHEN t1.Amount >= t2.col3 AND t1.Amount < t2.col4 THEN 3
        WHEN t1.Amount >= t2.col4 AND t1.Amount < t2.col5 THEN 4
        WHEN t1.Amount >= t2.col5 THEN 5 
        END AS Amount_VLOOKUP        
    FROM table2 t2
) AS t2
1 Like