Archives of the TeradataForum

Message Posted: Wed, 14 May 2003 @ 16:03:09 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Here is some code that Dieter helped me with a while back that calculates tenure (# of months since the customer installed). Sorry for the messy code...but basically I'm trying to set the tenure for a group of customers as of Mar03 monthend (canceled customers have a diff formula then and active customer)...I had to work around some database date anomalies.

Thanks,

Bruce

case
when curr_ent_id_cd='active' then
        (case when orgnl_srv_dt < date '1970-01-01' or orgnl_srv_dt > date
'2003-03-31' or orgnl_srv_dt is null then interval '0' month
        else ((date '2003-03-31' - orgnl_srv_dt) month(4)) end)
when curr_ent_id_cd='canceled' then
        (case when orgnl_srv_dt < date '1970-01-01' or orgnl_srv_dt > date
'2003-03-31' or cancel_date < date '1970-01-01' or cancel_date is null
or
orgnl_srv_dt is null then interval '0' month
        else ((cancel_date - orgnl_srv_dt) month(4)) end)
        end as tenure,

	<Prev	Next>		<<First	<Prev	Next>	Last>>