none
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

Answers

  • 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 Solution
    SELECT @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


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by JackJHolmes 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 Solution
    SELECT @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


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by JackJHolmes 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
    Moderator
  • 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.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, March 4, 2014 11:02 AM