编辑:进一步简化,并修复了< 与 <= 的错误。
无浮点数,适用于我能访问的所有数据库:
create table calls (t1 int, t2 int, rct int, cd int)
insert into calls (t1, t2, rct, cd)
values (60, 10, 48, 60)
insert into calls (t1, t2, rct, cd)
values (60, 10, 65, 70)
insert into calls (t1, t2, rct, cd)
values (60, 10, 121, 130)
insert into calls (t1, t2, rct, cd)
values (30, 20, 25, 30)
insert into calls (t1, t2, rct, cd)
values (30, 20, 35, 50)
insert into calls (t1, t2, rct, cd)
values (30, 20, 65, 70)
--Additional test to show that it works
insert into calls (t1, t2, rct, cd)
values (60, 10, 70, 70)
select t1, t2, rct, cd,
t1 + case when rct <= t1
then 0
else ( (rct-1-t1) / t2 + 1) * t2 end as CalceCD
from calls
结果:
t1 t2 rct cd CalceCD
----------- ----------- ----------- ----------- -----------
60 10 48 60 60
60 10 65 70 70
60 10 121 130 130
30 20 25 30 30
30 20 35 50 50
30 20 65 70 70
60 10 70 70 70
(6 行受影响)
您可以自由地创建函数作为UDF或任何SQL环境允许的内容,以清理选择。
编辑:是的,使用floor和偏移量1可以避免浮点数运算。