Mathematical formula for calculating call duration.

Question

Hello,

I was working for a telecom company some years ago and I had to generate a formula which calculates duration of a call according to the following algorithm:

• t1 is the first period
• t2 is the recurring period
• RCT is the actual call time (in seconds)
• CD is the effective call duration (for billing purposes)

if RCT is less than t1, then the CD equals t1
if RCT is greater than t1, then CD = t1 + x*t2, where x will "round" RCT to the next highest multiple of t2.

This algorithm translates to: "Charge for the first t1 seconds, then charge every t2 seconds after that".

Example:

t1 t2 RCT CD 
60 10 48 60
60 10 65 70
60 10 121 130
30 20 25 30
30 20 35 50
30 20 65 70

Can you create a function / SQL that will return the "call duration" CD?

Without using if then else ...?

Answer 1

EDIT: simplified further, and fixed < vs <= error.

No floating point and worked on every database I have access to:

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

Result:

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 row(s) affected)

You would be free to create the function as a UDF or whatever your SQL environment allows to clean up the select.

Edit: yes, floor and an offset of one avoids floating math.

Answer 2

Assuming int columns:

SELECT t1
    ,t2
    ,RCT
    CASE
    WHEN RCT < t1
        THEN t1 
    ELSE
        t1 + t2 * ((RCT - t1) / t2 + SIGN((RCT - t1) % t2))
    END AS CD

But I guess there is still one CASE, let me see if I can get rid of it.

With only integer arithmetic (still not ANSI):

SELECT  t1
       ,t2
       ,RCT
       ,CD
       ,t1 + SIGN(RCT / t1) * t2 * ((RCT - t1) / t2 + SIGN((RCT - t1) % t2)) AS CalcCD
FROM    Calls

Answer 3

I would use:

t1 + t2*ceiling( (rct - t1 + abs(rct - t1))*1.00/(2*t2) )

Or:

t1 + t2*ceiling( Cast((rct - t1 + abs(rct - t1)) as float)/(2*t2) )

Answer 4

I'm very sorry; I've deleted my answer, which I now realise was complete BS. MAX is a row aggregating operator.