# Trouble using a tariff table with banded pricing units to calculate a total price

### Question

• I have the task of replicating a pricing model in SQL so that I can do some analysis on theoretical changes to tariffs. The tariffs I am working with are to do with distances travelled, and the price per distance unit varies at different break points in the journey. I need a function which will apply the tariff to a variable distance value, which is proving to be a real challenge. Once I have this set up I will need to be able to run for up to 3 million journeys at a time so I will need something pretty speedy.

I am using SQL Server 2012 with SSMS

Here is the CREATE script for the tariff table:

```CREATE TABLE [dbo].[tariff_ratebrek](
[break_seq_num] [int] NULL,
[break_amount] [float] NULL,
[break_unit] [float] NULL,
[break_point] [float] NULL
) ```

And here are some made-up sample values to enter:

```INSERT INTO tariff_ratebrek_test
VALUES (0,400,0,0),
(1,100,0,2),
(2,200,1,3),
(3,150,1,5),
(4,250,1,6),
(5,400,1,7),
(6,250,1,10),
(7,220,1,28),
(8,200,1,55),
(9,150,1,99999)```

The resulting table should look like this:

 break_seq_num break_amount break_unit break_point 0 400 0 0 1 100 0 2 2 200 1 3 3 150 1 5 4 250 1 6 5 400 1 7 6 250 1 10 7 220 1 28 8 200 1 55 9 150 1 99999

So, the code I need should allow me to take an integer value representing distance and work out what the total price of the journey is, with these rules:

• break_amount is the price per break_unit for distance travelled within each band
• The start point of each band = break_point of the previous band + 1 (when ordered by break_seq_num)
• The end point of each band = break_point
• Where break_point = 0 the break_amount is included in the total price before any distance is travelled
• Where break_unit = 0 the break_amount is the total price for the band
• Otherwise, break_unit determines the units of distance at which the price increases by the break_amount

So for example, if a journey travelled a total distance of 12, the calculation would be:

0 miles travelled = 400

+ 0-2 miles @ 100 all-in = 500

+ 3rd mile @ 200/mile = 700

+ 4-5 miles @ 150/mile = 1000

+ 6th mile @ 250/mile = 1250

+ 7th mile @ 400/mile = 1650

+ 8-10 miles @ 250/mile = 2400

+ 11-12 miles @ 220/miles = 2840

If anyone knows a clever way of doing this please let me know as I am struggling and this would really unlock some amazing analytics opportunities

Thanks

Jack

Monday, March 3, 2014 6:43 PM

• Its easy if you're on SQL 2012

see

```-- variable to hold distance travelled (your example 12)
DECLARE @DistTravelled int =12
--Variable to get result
DECLARE @TotalJourneyPrice int

--Actual SolutionSELECT @TotalJourneyPrice = SUM(break_amount * CASE WHEN break_unit=0 THEN 1 ELSE COALESCE(NULLIF((CASE WHEN Break_point <= @DistTravelled THEN Break_point ELSE  @DistTravelled END - Start_point +1),0),1)/break_unit END )
FROM
(
SELECT *,LAG(break_point,1,0) OVER (ORDER BY break_seq_num)+1 AS Start_point
FROM tariff_ratebrek
)t
WHERE Break_point <= @DistTravelled
OR @DistTravelled BETWEEN Start_point AND  Break_point

SELECT @TotalJourneyPrice

Output
-----------------------
2840```

Here's one more example

```DECLARE @DistTravelled int =35

DECLARE @TotalJourneyPice int
SELECT @TotalJourneyPice=SUM(break_amount * CASE WHEN break_unit=0 THEN 1 ELSE COALESCE(NULLIF((CASE WHEN Break_point <= @DistTravelled THEN Break_point ELSE  @DistTravelled END - Start_point +1),0),1)/break_unit END )
FROM
(
SELECT *,LAG(break_point,1,0) OVER (ORDER BY break_seq_num)+1 AS Start_point
FROM tariff_ratebrek
)t
WHERE Break_point <= @DistTravelled
OR @DistTravelled BETWEEN Start_point AND  Break_point

SELECT @TotalJourneyPice

Output
----------
7760```

Now check the logic

0 miles travelled = 400

+ 0-2 miles @ 100 all-in = 500

+ 3rd mile @ 200/mile = 700

+ 4-5 miles @ 150/mile = 1000

+ 6th mile @ 250/mile = 1250

+ 7th mile @ 400/mile = 1650

+ 8-10 miles @ 250/mile = 2400

+ 11-28 miles @ 220/miles = 6360 (2400 + (220 * 18))

+ 29-35 miles @200/miles = 7760 (6360 + (200 * 7))

Hope this is what you're after

• Marked as answer by Tuesday, March 4, 2014 10:06 AM
Monday, March 3, 2014 7:29 PM

### All replies

• Its easy if you're on SQL 2012

see

```-- variable to hold distance travelled (your example 12)
DECLARE @DistTravelled int =12
--Variable to get result
DECLARE @TotalJourneyPrice int

--Actual SolutionSELECT @TotalJourneyPrice = SUM(break_amount * CASE WHEN break_unit=0 THEN 1 ELSE COALESCE(NULLIF((CASE WHEN Break_point <= @DistTravelled THEN Break_point ELSE  @DistTravelled END - Start_point +1),0),1)/break_unit END )
FROM
(
SELECT *,LAG(break_point,1,0) OVER (ORDER BY break_seq_num)+1 AS Start_point
FROM tariff_ratebrek
)t
WHERE Break_point <= @DistTravelled
OR @DistTravelled BETWEEN Start_point AND  Break_point

SELECT @TotalJourneyPrice

Output
-----------------------
2840```

Here's one more example

```DECLARE @DistTravelled int =35

DECLARE @TotalJourneyPice int
SELECT @TotalJourneyPice=SUM(break_amount * CASE WHEN break_unit=0 THEN 1 ELSE COALESCE(NULLIF((CASE WHEN Break_point <= @DistTravelled THEN Break_point ELSE  @DistTravelled END - Start_point +1),0),1)/break_unit END )
FROM
(
SELECT *,LAG(break_point,1,0) OVER (ORDER BY break_seq_num)+1 AS Start_point
FROM tariff_ratebrek
)t
WHERE Break_point <= @DistTravelled
OR @DistTravelled BETWEEN Start_point AND  Break_point

SELECT @TotalJourneyPice

Output
----------
7760```

Now check the logic

0 miles travelled = 400

+ 0-2 miles @ 100 all-in = 500

+ 3rd mile @ 200/mile = 700

+ 4-5 miles @ 150/mile = 1000

+ 6th mile @ 250/mile = 1250

+ 7th mile @ 400/mile = 1650

+ 8-10 miles @ 250/mile = 2400

+ 11-28 miles @ 220/miles = 6360 (2400 + (220 * 18))

+ 29-35 miles @200/miles = 7760 (6360 + (200 * 7))

Hope this is what you're after

• Marked as answer by Tuesday, March 4, 2014 10:06 AM
Monday, March 3, 2014 7:29 PM
• ```CREATE TABLE [dbo].[tariff_ratebrek](
[break_seq_num] [int] NULL,
[break_amount] [float] NULL,
[break_unit] [float] NULL,
[break_point] [float] NULL
)

INSERT INTO tariff_ratebrek
VALUES (0,400,0,0),
(1,100,0,2),
(2,200,1,3),
(3,150,1,5),
(4,250,1,6),
(5,400,1,7),
(6,250,1,10),
(7,220,1,28),
(8,200,1,55),
(9,150,1,9999)

;WITH Num1 (num) AS (
SELECT 1 as num
UNION ALL SELECT num+1 as n
FROM Num1 Where num<101),
Num2 (num) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (num) AS (SELECT ROW_NUMBER() OVER(ORDER BY num)-1 FROM Num2)

,mycte as
(
select [break_seq_num], break_amount,  [break_unit], t.break_point, d.num ,  row_number()over(order by num ) rn
from [tariff_ratebrek] t
RIGHT Join  Nums d

on t.break_point=d.num
)

,mycte1 as(
Select break_unit, bu,[break_point], bp,  ba, break_amount, m.num from mycte m
Cross apply (select top 1 break_unit from mycte m1 WHERE m.num>=m1.num and break_unit is not null Order by num DESC) d1(bu)
Cross apply (select top 1 [break_point] from mycte m2 WHERE m.num>=m2.num and break_point is not null   Order by num DESC) d2(bp)
outer APPLY (SELECT TOP 1 break_amount FROM   mycte WHERE  rn >= m.rn AND break_amount IS NOT NULL   ORDER  BY rn  ) d3( ba)
)

select top 1  num, bu as [break_unit],bp as [break_point], ba as break_amount
,  sum(Case WHEN bu=0 and bp=0 and num<>0 Then 0 Else ba End ) Over(Order by num) amt
from mycte1
WHere num<=12
ORDER  BY num DESC

option(maxrecursion 999)

drop table [tariff_ratebrek]```

Monday, March 3, 2014 9:32 PM
• This is great, thanks so much
Tuesday, March 4, 2014 10:06 AM
• Hi

This works really well when I am passing the variables to it but when I include the code in a query something very strange happens.

I have a query which I use to pull the rate code and the distance. When I join to the subquery in the above code and add the calculation to the select, some of the prices do not calculate correctly.

I have found that, in some cases, the start point derived from the subquery has moved out of sync with the break points e.g. a tariff which has break points at 0, 2, 10 calculates the first three start points as 1, 1, 3 (correctly) when I pass the variables to the function. If I include the code in the larger query it calculates the first two start points as 1, 3.

Any idea why this might be happening?

Tuesday, March 4, 2014 10:50 AM
• ...don't worry, I've fixed it
Tuesday, March 4, 2014 10:58 AM
• ...don't worry, I've fixed it

Cool

Glad that you got it sorted

Thanks for sharing this interesting problem to work with.