我需要从累积值计算一个百分比.要应用于每行中每个值的百分比率取决于从另一个表中获取的费率.百分比率计算需要以分层方式进行,因为税收可能会根据收入计算.
例如：
工资= 1000
600 * 10％[以较低税率计算的首600美元]
400 * 30％[以较高税率计算的剩余金额]
所以,我一直试图让这个工作,但不能解决它. DBA离开了,所以它被移交给我.大多数sql我都没问题,但我不知道如何处理这个问题,或者我应该在谷歌搜索的内容,所以道歉这是一个简单的搜索,请指导我到URL和我’我会尝试自己解决！
无论如何,下面是数据表(#v)格式的示例和范围表(#tiers)的示例,以及到目前为止我的方法.我需要一个新的列,按照我上面的解释,以正确的百分比级别计算’cval’.
希望有人可以帮助或指出我正确的方向！
谢谢,J.
create table #v(
id nvarchar(50),val money,tid int
)
insert into #v values ('a',30,1)
insert into #v values ('b',50,1)
insert into #v values ('c',10,1)
insert into #v values ('d',1)
insert into #v values ('e',-80,1)
create table #tiers (
tid int,threshold money,amount money
)
insert into #tiers values (1,30)
insert into #tiers values (1,40,40)
insert into #tiers values (1,100,50)
select * from
(
select v1.id,v1.tid,v1.val,sum(v2.val) cval
from #v v1
inner join #v v2 on v1.id >= v2.id
group by v1.id,v1.tid
) a
left join
(
select a.tid,a.id,a.threshold [lower],b.threshold [upper] from
(
select rank() over (order by threshold) as id,tid,threshold,amount from #tiers
) a
left join
(
select rank() over (order by threshold) as id,amount from #tiers
) b on a.id = b.id-1
) b on (a.cval >= lower and a.cval < upper) or (a.cval >= lower and upper is null)
解决方法
假设Tiers表中的Amount列应该是税率,您可以执行以下操作：
With VData As
(
Select V1.id,V1.val,V1.tid,Sum(V2.val) As CVal
From #V As V1
Join #V As V2
On V2.id <= V1.id
Group By V1.id,V1.tid
),Tiers As
(
Select T1.tid,T1.Amount,T1.threshold As MinThreshold,Min(Coalesce(T2.threshold,2147483647)) As MaxThreshold
From #tiers As T1
Left Join #tiers As T2
On T2.threshold > T1.threshold
Group By T1.tid,T1.threshold
)
Select V.id,V.val,V.tid,V.CVal,Sum(
Case
When CVal > T.MaxThreshold Then T.Amount / 100.00 * T.MaxThreshold
When CVal >= T.MinThreshold Then T.Amount / 100.00 * (V.CVal - T.MinThreshold)
End) As TotalTax
From VData As V
Join Tiers As T
On T.tid = V.tid
Group By V.id,V.CVal
