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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy