Teradata Syntax

Tools
Enthusiast

Teradata Syntax

Can someone tell me the syntax to round billed mins to the nearest minute?

Eg

From To
Billed Mins New Billed Mins
--------------- ------------------------
6.4 7.0
6.5 7.0
6.6 7.0

In SAS it is ‘CEIL’.
this is the Teradata script that I'm using line 10 not good...
select a.btn_npa,
a.btn_nxx,
a.btn_line_no,
a.org_tn,
a.connect_tm,
a.msg_value_amt,
a.chargeable_drtn,
a.conversation_drtn,
(a.msg_value_amt/a.chargeable_drtn) as arpm,
ceil(a.chargeable_drtn),0) as new_hold_tm
from prod.vbusmsg a
inner join
(select start_dt,end_dt from prod.vrptmth
where reporting_yr=2006 and reporting_mth= 02) as p
on (a.billed_dt between p.start_dt and p.end_dt)
where
a.btn_npa = '416' and a.btn_nxx = '667' and a.btn_line_no = '5555' and
a.org_tn = '4166675555' and (a.msg_value_amt > 0 and
a.chargeable_drtn > 0)
group by 1,2,3,4,5,6,7,8,9,10)
2 REPLIES
Junior Contributor

Re: Teradata Syntax

Hi Vincent,
in V2R5.1+ the easiest way would be a UDF.

If a.chargeable_drtn is a decimal > 0:

case when (x mod 1) = 0 then x else x + 1 - (x mod 1) end

or:

cast(x + 0.9 as int) -- for dec(x,1)
cast(x + 0.99 as int) -- for dec(x,2)

Dieter
Highlighted
Enthusiast

Re: Teradata Syntax

Thanks Dieter.. I tried the cast and worked fine!....