CODEWITHSUNDEEP
sql-server
Ali Hussain
Ali Hussain

Reputation: 3

Compare two different column in sql server to get result

i'm stuck in problem, i want to get result who's value falls between SlabFrom & Slabto

Query:

Declare @Userinputvalue Decimal(11,6);
Set     @Userinputvalue = '700001';

select * from _TaxRate

where   SlabTo >= @Userinputvalue and SlabFrom <= @Userinputvalue

TABLE DATA (EXISTING DATA)

SRNO    SlabFrom    SlabTo  Perage
-----------------------------------------
1      0             600000    0
2      600001        1200000   5
3      1200001       1500000   7
4      1500001       2000000   10

Above Result

No Data Found / Blank

But, I Need suppose row number two because my user input value found between (600001 - 1200000), but above query return no data.

Any Help will be highly appreciated.

Upvotes: 0

Views: 53

Answers (1)

SteveC
SteveC

Reputation: 6015

If you run this code you'll see why

Declare @Userinputvalue Decimal(11,6);
Set     @Userinputvalue = '700001';
select @Userinputvalue;

The result is arithmetic overflow. Decimal(11, 6) is not wide enough to store '700001'

Suppose all of the columns are integers, then it works without issues

Data

drop table if exists #tTest;
go
create table #tTest(
  SRNO               int,
  SlabFrom           int,
  SlabTo             int,
  Perage             int);
  
insert #tTest(SRNO, SlabFrom, SlabTo, Perage) values
(1, 0, 600000, 0),
(2, 600001, 1200000, 0),
(3, 1200001, 1500000, 0),
(4, 1500001, 2000000, 0);

Query

Declare @Userinputvalue int;
Set     @Userinputvalue = 700001;

select *
from #tTest t 
where SlabTo >= @Userinputvalue
      and SlabFrom <= @Userinputvalue;

Output

SRNO    SlabFrom    SlabTo  Perage
2       600001      1200000 0

Upvotes: 1

Related Questions