SQLTeam.com | Weblogs | Forums

VLOOKUP Equivalent in SQL

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

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

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