27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[DR](
[identifier] [int] IDENTITY(1,1) NOT NULL,
[PROVINCE] [varchar](100) NOT NULL,--省
[City] [varchar](100) NOT NULL,--市
[StartAmount] [decimal](20, 2) NOT NULL,--开始金额
[EndAmount] [decimal](20, 2) NOT NULL,--结束金额
[CommissionRate] [decimal](10, 2) NOT NULL,--返点率
)
insert into DR (PROVINCE,City,StartAmount,EndAmount,CommissionRate)
values ('110000','110000',0,100000,0.1)
insert into DR (PROVINCE,City,StartAmount,EndAmount,CommissionRate)
values ('110000','110000',100001,200000,0.15)
insert into DR (PROVINCE,City,StartAmount,EndAmount,CommissionRate)
values ('110000','110000',200001,1000000000,0.2)
declare @JE1 decimal
declare @JE decimal
SELECT @JE1=80000,@JE=50000
--计算已缴
select (@JE1-STARTAMOUNT) * CommissionRate + BaseRtaeAmount
FROM DR WHERE @JE1 > STARTAMOUNT AND @JE1<= ENDAMOUNT
--计算总缴
select (@JE+@JE1- STARTAMOUNT)* CommissionRate + BaseRtaeAmount
FROM DR WHERE @JE+@JE1 > STARTAMOUNT AND @JE+@JE1<= ENDAMOUNT
declare @JE1 decimal
declare @JE decimal
SELECT @JE1=80000,@JE=50000
select SUM( CASE WHEN @JE+@JE1>=EndAmount THEN
(EndAmount-StartAmount )*CommissionRate
WHEN @JE+@JE1>=StartAMount THEN
(@JE+@JE1-StartAmount )*CommissionRate ELSE 0 END ) 总应缴,
SUM(CASE WHEN @JE1 >=EndAmount THEN
(EndAmount-StartAmount )*CommissionRate
WHEN @JE1 >=StartAMount THEN
( +@JE1-StartAmount )*CommissionRate ELSE 0 END) 已缴
FROM DR