question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked nasreen-akter commented

R_Type_Name according to Range

I want R_Typ_Range according to range ,below is data

  Create table #tbl_Range (R_type_ID int,R_Type_Name varchar(50),R_Value_From decimal(10,4),R_Value_to decimal(10,4))
  Create table #tbl_issuance  (issue_ID int,item_code int,Issue_Date int,Value decimal(10,4))
    
  Insert into #tbl_Range values(1,'A',0.1000 ,0.5000)
   Insert into #tbl_Range values(2,'B',0.6000 ,0.8000)
    Insert into #tbl_Range values(3,'C',0.9000 ,0.10000)
    
    insert into #tbl_issuance  values (1001,1,'2021-10-20',0.2200)
    insert into #tbl_issuance  values (1002,2,'2021-10-20',0.6200)
    insert into #tbl_issuance  values (1003,2,'2021-10-20',0.9200)  

if value in #tbl_issuance exit between 0.1000 to 0.5000 in #tbl_Range table then R_Type_Name will be 'A'
if value in #tbl_issuance exit between 0.6000 to 0.8000 in #tbl_Range table then R_Type_Name will be 'B'
if value in #tbl_issuance exit between 0.9000 to 0.10000 in #tbl_Range table then R_Type_Name will be 'C'


Output

142079-image.png


sql-server-generalsql-server-transact-sql
image.png (7.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

nasreen-akter avatar image
0 Votes"
nasreen-akter answered nasreen-akter commented

Hi @akhterhussain-3167,

Would you please try the following. Thanks!

 Create table #tbl_Range (R_type_ID int,R_Type_Name varchar(50),R_Value_From decimal(10,4),R_Value_to decimal(10,4))
 Create table #tbl_issuance  (issue_ID int,item_code int,Issue_Date date, Value decimal(10,4))
        
 Insert into #tbl_Range values(1,'A',0.1000 ,0.5000)
 Insert into #tbl_Range values(2,'B',0.6000 ,0.8000)
 Insert into #tbl_Range values(3,'C',0.9000 ,1.0000)
        
 insert into #tbl_issuance  values (1001,1,'2021-10-20',0.2200)
 insert into #tbl_issuance  values (1002,2,'2021-10-20',0.6200)
 insert into #tbl_issuance  values (1003,2,'2021-10-20',0.9200)  
    
 select i.issue_ID, i.item_code, FORMAT(i.Issue_Date, 'MM/dd/yyyy') as Issue_Date, i.Value, r.R_Type_Name from #tbl_issuance as i
 left join #tbl_Range as r on (i.Value >= r.R_Value_From AND i.Value <= r.R_Value_to)

142018-image.png




image.png (59.6 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

it is not working in my Actual Data below is image.

142108-image.png




with below ranges

  Insert into #tbl_Range values(1,'C',0.0001 ,0.1999)
  Insert into #tbl_Range values(2,'B',0.2000 ,0.3399)
  Insert into #tbl_Range values(3,'A',0.3400 ,0.9999)
0 Votes 0 ·
image.png (19.2 KiB)

Hi @akhterhussain-3167, sorry to hear that. would you please share some real examples with insert statement. Thanks!

0 Votes 0 ·