Query

Transact-SQL
1

i have a table range with field productid pricerange1 pricerange2

ProductId PriceRange1 PriceRange2

1 10-20 20-30

i want to write a query where i want to select pricerange1 10-20,if i enter price 15 and product id=1, if i select price 25 and product id=1 then want the pricerange2 20-30

2

First, if you still have the option to do so, change the design so you have five columns in stead of the 3 as
ProductId MinPrice1 MaxPrice1 MinPrice2 MaxPrice2
That will make querying much easier.

Second, if you have a price = 20, what do you want to get? 10-20 or 20-30?

What do you want to return if the price is less than 10 or greater than 30?

If you can have many price ranges, you should change the table so it has four columns Product Id, Range Id, MinPrice, MaxPrice.

All that said, querying is a bit messy in the current set up.

DECLARE
	@productId INT = 1,
	@price INT = 20;
	

SELECT 
	CASE 
		WHEN @Price >= CAST(LEFT(PriceRange1,CHARINDEX('-',PriceRange1+'-')-1) AS INT) 
			AND @Price < CAST(STUFF(PriceRange1,1,CHARINDEX('-',PriceRange1+'-'),'') AS INT)
		THEN '10-20'
		WHEN @Price >= CAST(LEFT(PriceRange2,CHARINDEX('-',PriceRange2+'-')-1) AS INT) 
			AND @Price < CAST(STUFF(PriceRange2,1,CHARINDEX('-',PriceRange2+'-'),'') AS INT)
		THEN '10-20'
		ELSE '???'
	END
FROM
	YourTable;
3

Thanks a lot. I'll go as per your suggestion.