February 22, 2018 at 2:23 pm
Hi, Below the sample data to play with,
Declare @test-2 table(Id int,Name varchar(50), Value int);
select 1,'Test1',0 union all
select 2,'Test2',1000 union all
select 3,'Test3',2000 union all
select 4,'Test4',3000
If I pass the Value as 60 it should return Test1 because it didn't reach 1000 yet
If I pass the Value as 1001 it should return Test2 becuse it didnot reach 2000 yet
If I pass the Value as 1500 it should return Test3 becuse it didnot reach 3000 yet
If I pass the Value as 3001 it should return Test4 becuse it reached 3000.
how to dynamically check the range of values based on another value and get the row. Any sample query please
February 22, 2018 at 3:55 pm
If I understand the logic correctly, shouldn't 1500 return Test2 for the same reason as 1001? Maybe it was a typo and you meant 2500; if not, I'll definitely need the logic explained 🙂
Otherwise, it seems like you're just wanting the name corresponding to highest value in the table that is less than some value you pass in, so something like this:
DECLARE @test-2 TABLE(Id INT,[Name] VARCHAR(50), [Value] INT);
DECLARE @some_parameter INT=60;
INSERT INTO @test-2
select 1,'Test1',0 UNION ALL
select 2,'Test2',1000 UNION ALL
select 3,'Test3',2000 UNION ALL
select 4,'Test4',3000;
SELECT TOP 1 [Name]
FROM @Test
WHERE [Value]<@some_parameter
ORDER BY [Value] DESC;
One thing not entirely clear from the examples is what happens when the value passed in is equal to a value in the table. Should a passed value of 1000 return Test1 or Test2? If it should return Test1, then the query works as-is. If it should return Test2, then the '<' should be changed to '<='.
Cheers!
February 28, 2018 at 8:59 am
Jacob Wilkins - Thursday, February 22, 2018 3:55 PMIf I understand the logic correctly, shouldn't 1500 return Test2 for the same reason as 1001? Maybe it was a typo and you meant 2500; if not, I'll definitely need the logic explained 🙂Otherwise, it seems like you're just wanting the name corresponding to highest value in the table that is less than some value you pass in, so something like this:
DECLARE @test-2 TABLE(Id INT,[Name] VARCHAR(50), [Value] INT);
DECLARE @some_parameter INT=60;INSERT INTO @test-2
select 1,'Test1',0 UNION ALL
select 2,'Test2',1000 UNION ALL
select 3,'Test3',2000 UNION ALL
select 4,'Test4',3000;SELECT TOP 1 [Name]
FROM @Test
WHERE [Value]<@some_parameter
ORDER BY [Value] DESC;One thing not entirely clear from the examples is what happens when the value passed in is equal to a value in the table. Should a passed value of 1000 return Test1 or Test2? If it should return Test1, then the query works as-is. If it should return Test2, then the '<' should be changed to '<='.
Cheers!
Hi Jacob, sorry for late reply. yes you are corrcect. if the value passed as 1500 then it should give Test2.
If the value equals for example 1000 then it should be Test2. 2000 it should be Test3.
your solution works perfectly with <=. thanks a lot
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply