SQL:根据每个范围内的不同关税计算使用量 [英] SQL: Calculate usage amount based on varying tariff in each range
问题描述
大家好,
我有一张使用表和资费表,使用金额必须根据资费表计算。但是,它会在每个范围内变化。类似的问题我问过,但我忘了包含这种不同的关税规则。
I have a usage table and tariff table, and the usage amount has to be calculated based on the tariff table. But, it will will vary in each range. Similar question I asked but I forgot to include this varying tariff rule.
例如:如果我的使用量为350,金额计算将
For eg: if I have a usage of 350 and the amount calculation will be
0-100 - > .23 * 100
0-100 -->.23 *100
100-300 - > 0.34 * 200
100-300 --> 0.34 *200
> 300 - > 0.45 * 50
>300 --> 0.45 * 50
如何在sql中实现此目的?
How can I achieve this in sql?
CREATE TABLE用法
(user_id CHAR(5) NOT NULL PRIMARY KEY,
amt float NOT NULL
)
CREATE TABLE Usage
(user_id CHAR(5) NOT NULL PRIMARY KEY,
amt float NOT NULL
)
INSERT INTO Usage
VALUES('AE200', 100.5),('BE400', 375);
INSERT INTO Usage
VALUES ('AE200', 100.5), ('BE400', 375);
创建表格关税
(
CREATE TABLE Tariffs
(
tariff_id int 身份主键
tariff_id int identity primary key
slab_start INTEGER NOT NULL,
slab_end INTEGER NOT NULL,
CHECK(slab_start< slab_end),
tariff_rate DECIMAL(5,2)NOT NULL
);
INSERT INTO Foobar_Tariffs
VALUES
(0,100,0.23) ,(100,300,.34)(300,999999,.45);
slab_start INTEGER NOT NULL ,
slab_end INTEGER NOT NULL,
CHECK (slab_start < slab_end),
tariff_rate DECIMAL (5,2) NOT NULL
);
INSERT INTO Foobar_Tariffs
VALUES
(0, 100, 0.23), (100,300, .34) (300,999999, .45);
谢谢
朱莉
推荐答案
Select u.user_id, u.amt,
Sum(Case When u.amt Between t.slab_start And t.slab_end Then t.tariff_rate * (u.amt - t.slab_start) Else 0 End
+ Case When u.amt > t.slab_end Then t.tariff_rate * (t.slab_end - t.slab_start) Else 0 End) As TotalTariff
From Usage u
Cross Join Tariffs t
Group By u.user_id, u.amt;
Tom
Tom
这篇关于SQL:根据每个范围内的不同关税计算使用量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!