Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Date Arithmetic
 
From:   Burton, Bruce

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023