I'm not quite sure what this type of query is, I thought perhaps a CASE statement or do I use BETWEEN? This is the data I have:

It is basically what you would see on an ecommerce website when buying products. I want to fire in a qty & sku to this query (let's say 3), it would then know that 3 is between 1 and 5 and return me 37.50 or if a customer orders 12 it would return 120.00 etc.

As an update, this is how far I got:

DECLARE @sku nvarchar(50)
DECLARE @qty int
SET @sku = 'SKU-1'
SET @qty = 5
SELECT TOP 1 (price * @qty) FROM global_tier_pricing WHERE sku = @sku

This works until I send in a qty of 5, I would expect it to be (5*11) and be 55.00 but it says 62.50

CodePudding user response：

Assuming you can only have one price for a given sku and qty, you can filter for the right price in the where clause before the arithmetic

declare @qty int=3;
declare @sku varchar(100)='SKU-1';

select @qty * price
from your_table
where sku=@sku and qty=(select max(qty) from your_table where qty<=@qty)