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
May 14, 2021 at 1:20 pm
A case statement should do it:
-- Change operators appropriately if not inclusive
CASE WHEN Amount >= Column1 AND Amount <= Column2 THEN 1
WHEN Amount >= Column2 AND Amount <= Column3 THEN 2
WHEN Amount >= Column3 AND Amount <= Column4 THEN 3
WHEN Amount >= Column4 AND Amount <= Column5 THEN 4
END
-- between syntax will work if inclusive:
CASE WHEN Amount BETWEEN Column1 AND Column2 THEN 1
WHEN Amount BETWEEN Column2 AND Column3 THEN 2
WHEN Amount BETWEEN Column3 AND Column4 THEN 3
WHEN Amount BETWEEN Column4 AND Column5 THEN 4
END
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
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 14, 2021 at 3:11 pm
If you include the Vlookup amount in your second table, you won't even require a case statement.
declare @Table1 table (ID int identity(1,1) primary key, Amount int)
insert into @Table1
Values (75),(22), (0),(105),(6),(11)
declare @Table2 table (Col1 int primary key, Col2 int, AmountVLookup tinyint)
insert into @table2
values(-999999999,1, 0)
,(1,10,1)
,(11,30,2)
,(31,50,3)
,(51,100,4)
,(101,999999999,5)
select t1.Id, t1.Amount, t2.AmountVLookup
from @Table1 t1
join @Table2 t2 on t1.Amount >= Col1 and t1.Amount <= Col2
A SQL JOIN is analogous to an Excel VLookup.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 17, 2021 at 7:34 am
Thank you so much, this almost worked but I am only getting values of '5' for Amount_VLOOKUP. Any way of fixing this?
May 17, 2021 at 7:47 am
Actually I managed to fix it by rearranging the values for t2! Thank you so much for your help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply