Using a tariff table with banded pricing units to calculate a total price


http://social.msdn.microsoft.com/Forums/en-US/0aed0dfd-5e82-47c5-a4d6-d10f94f07344/trouble-using-a-tariff-table-with-banded-pricing-units-to-calculate-a-total-price?forum=transactsql


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]


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s