case condition in min max values

Question

Hi Expert,

I wanted to update the target table based on the conversation table . if the value is between 100 and 200 then

if the value is between 200 and 400 then

create table source1
(car int,cycle int)
insert into source1
values(150,250)

create table conversation_table
(min int ,max int, speed char, rating char)
insert into conversation_table
values(100,200,'Higher','good'),
(200,400,'Highest','better')

create table target1
(property char, value int, speed char, rating char)
expected output

Answer 1

create table source1  
(car int,cycle int)  
insert into source1  
values(150,250)  
   
  
create table conversation_table  
(min int ,max int, speed  varchar(30), rating varchar(30))  
insert into conversation_table  
values(100,200,'Higher','good'),  
(200,400,'Highest','better')  
   
;with mycte   (TheKey, TheValue) as (  
SELECT [KEY], Value from OpenJson((  
select * from source1  FOR JSON AUTO ))  
)   
    
 SELECT unpvt.[Key] as Property, ct.max as Value, ct.speed,ct.rating  
 FROM mycte AS src  
 CROSS APPLY OpenJson(src.TheValue) AS unpvt    
Join conversation_table ct on unpvt.Value between ct.min and ct.max  
  
  
drop table source1,conversation_table  

Answer 2

Any other suggestion with simply case condition or easiest one

Answer 3

 create table source1  
 (car int,cycle int)  
 insert into source1  
 values(150,250)  
       
      
 create table conversation_table  
 (min int ,max int, speed  varchar(30), rating varchar(30))  
 insert into conversation_table  
 values(100,200,'Higher','good'),  
 (200,400,'Highest','better')  
    
Select Property, ct.max as Value, ct.speed,ct.rating   
from source1  
Unpivot  
(  val for Property in (Car,cycle) ) as unpvt  
 Join conversation_table ct on unpvt.val between ct.min and ct.max  
      
      
 drop table source1,conversation_table  

Answer 4

Hi @Shambhu Rai

How about change the source1 table like below

  create table source1  
  (Property varchar(20),value int)  
  insert into source1  
  values('cycle',150),('car',250)  
           
          
  create table conversation_table  
  (min int ,max int, speed  varchar(30), rating varchar(30))  
  insert into conversation_table  
  values(100,200,'Higher','good'),  
  (200,400,'Highest','better')  
  
  select * from source1  cross apply( select top 1* from conversation_table  where source1.value >=conversation_table.min and source1.value < conversation_table.max) p  

result is like this:

Answer 5

Hi @Shambhu Rai
Please check this query:

;WITH CTE AS  
(  
 SELECT Property,[Value]  
 FROM source1   
 UNPIVOT([Value] FOR Property IN ([car],[cycle]))U  
)  
SELECT A.Property,B.max AS [Value],B.speed,B.rating  
FROM CTE A JOIN conversation_table B ON A.Value BETWEEN B.min AND B.max  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.