Mathematical formula for calculating call duration

Question

I worked at a telecommunications company several years ago, and I had to formulate a formula that calculates the call duration in accordance with the following algorithm:

• t1 - first period
• t2 - repeating 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 CD is t1 if RCT is greater than t1, then CD = t1 + x * t2, where x will bypass 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 / SQL function that will return a CD with the duration of the call?

Without using if then else ...?

Answer 1

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 think there is another CASE, let me see if I can get rid of it.

Only with integer arithmetic (not ANSI yet):

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

Answer 2

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

There is no floating point and work in every database that 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 could freely create a function like UDF or whatever your SQL environment allows you to clear the selection.

Edit: yes, gender and the offset of one avoids floating math.

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) )