CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Mazzy
Mazzy

Reputation: 1944

Quantity discount in SQL

I have got a table with the following structure:

id - int from - float 1 dec(km's) till - float 1 dec(km's) price float 2 dec

with the values

(1, 0, 9.9, 15),
(2, 10, 19.9, 11),
(3, 20, 24.9, 7)

What I need is a calculation of the price summed up

So let's say you travel 23 km Your first 9.9 km's will be 15eu/km Your 10-19.9 km will be 11eu/km Your final km's will be 7eu/km

Because I don't know how much rows this table will hold, how can I make a function or SP, that resolves the price for me?

The price will be: (9.9 - 0) * 15 + (19.9 - 10) * 11 + (3.2 * 7)

Upvotes: 1

Views: 247

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use the following query in order to calculate the total price for a predefined number of kms:

DECLARE @km INT = 23

SELECT SUM(IIF([to] < @km, [to] - [from], @km - [from]) * price)
FROM mytable
WHERE [from] <= @km

Demo here

Explanation:

The following query:

DECLARE @km INT = 23

SELECT IIF([to] < @km, [to] - [from], @km - [from]) AS km,  price
FROM #mytable
WHERE [from] <= @km

produces the following output:

km  price
=========
9,9 15
9,9 11
3   7

Upvotes: 3

Related Questions