7

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 ...?

Cade Roux
  • 88,164
  • 40
  • 182
  • 265

3 Answers3

4

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
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • Thanks, it's still messier than I think it can be. With conversion to float it's cleaner, but I bet measurably slower for BIG queries (like phone calls!). – Cade Roux Jan 30 '09 at 23:01
  • Ah well. No need for premature optimization :) If the OP ends up needing it to compute ten billion entries in less than a second, he'll have to post another question on how to improve the efficiency! – e.James Jan 30 '09 at 23:10
2

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.

Godeke
  • 16,131
  • 4
  • 62
  • 86
  • Shouldn't that be `ceiling((rct-t1)/t2)` instead of `(floor((rct-t1)/t2)+1)`? Otherwise, 60-10-70 would produce a CD of 80. – Ben Blank Jan 30 '09 at 22:21
  • insert into calls values (60, 10, 70, 70) insert into calls values (60, 10, 80, 80) These don't work – Cade Roux Jan 30 '09 at 22:26
  • Those inserts assume a syntax that can imply the format of the table. Add the (t1, t2, rct, cd) if needed. – Godeke Jan 30 '09 at 23:29
  • The use of floor with some "off by one" de-increments and re-increments avoids floating math and should work anywhere. There error was the missing -1 in the rct-1-t1. – Godeke Jan 30 '09 at 23:30
  • Very clever little shift there, and now you can take out floor and it's just as good! (for certain values, I imagine it must be incorrect - I'll have to find the values of rct, t1 and t2 for which it fails.) – Cade Roux Jan 31 '09 at 01:18
  • Hmmm, true... floor has become redundant there. Hopefully it won't fail for anything, although I guess it documents the intent. :) – Godeke Jan 31 '09 at 02:04
  • fails for: insert into calls (t1, t2, rct, cd) values (60, 10, 60, 60) – Cade Roux Jan 31 '09 at 02:22
  • BTW, you can get rid of the CASE the same way I did, by mutiplying by SIGN(RCT / t1) – Cade Roux Jan 31 '09 at 02:23
  • Good catch on 60,10,60,60. It should be '<=' not '<'. Fixed in body. Testing shows that the integer divide that happens is as good as floor, removed. – Godeke Jan 31 '09 at 22:22
2

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) )
e.James
  • 116,942
  • 41
  • 177
  • 214