CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Rohit Raghuvansi
Rohit Raghuvansi

Reputation: 2864

how to find a value between ranges in sql server

My condition is

My query is returning this

2             10          150000   where 2=startingslab and 10 = endingslab

11            20          250000

21            31          150000

now i want to get the price details for startingslab =3. i.e 15000.I know i need to process it row by row as between is not working.

Is there any way except cursors and while loop to accomplish this.

EDIT

This query returns the above resultset

SELECT  dbo.TBM_Slab_Details.SlabStartValue, 
        dbo.TBM_Slab_Details.SlabEndValue,
        convert(int,(@MRP-(dbo.TBM_Slab_Details.Discount*@MRP)/100)) as SlabPrice    
FROM    dbo.TBM_SLAB 
        INNER JOIN dbo.TBM_Slab_Details ON dbo.TBM_SLAB.SlabId = dbo.TBM_Slab_Details.SlabId and  productid=@productID

now i have a variable @slabvalue which holds slabvalue.

now for eg @slabvalue=3,I want 150000 from above resultset

if it is 12,I want 250000

Upvotes: 0

Views: 1646

Answers (3)

HLGEM
HLGEM

Reputation: 96552

If your SLABStartvalue and SlabEnd value are integer (or other numeric) data types, maybe this would work?

declare @Myvariable int
Set @Myvariable = 3

select @Myvariable, slabprice from 
(SELECT  dbo.TBM_Slab_Details.SlabStartValue,         
    dbo.TBM_Slab_Details.SlabEndValue,        
    convert(int,(@MRP-(dbo.TBM_Slab_Details.Discount*@MRP)/100)) as SlabPrice    
FROM    dbo.TBM_SLAB         
INNER JOIN dbo.TBM_Slab_Details 
    ON dbo.TBM_SLAB.SlabId = dbo.TBM_Slab_Details.SlabId and  productid=@productID) a
    where SlabStartValue <= @Myvariable and SlabEndValue>=@Myvariable

If they are character type of data you might need to convert them to ints in the where clause to get it to work.

Upvotes: 0

Austin Salonen
Austin Salonen

Reputation: 50215

SELECT  convert(int,(@MRP-(d.Discount*@MRP)/100)) as SlabPrice    
FROM    dbo.TBM_SLAB s
        INNER JOIN dbo.TBM_Slab_Details d ON s.SlabId = d.SlabId and productid=@productID
WHERE   @slabValue >= d.SlabStartValue
        and @slabValue <= d.SlabEndValue

Upvotes: 1

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

a quick blind shot:

declare @lab int
select top 1 *
from yourTable
where startingslab >= @lab
order by startingslab asc

Upvotes: 0

Related Questions