Archives of the TeradataForum

Message Posted: Sat, 22 May 2004 @ 08:43:06 GMT

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

Thanks for all the replies, but I wasn't very clear with my question.

The problem is how to advance an expired contract date to the next annual boundary relative to the current date, but based on the original ending month and day. The expiration could have been several years ago, so I can't just use the current year.

Here's a version I'm using right now. It's not very graceful and it doesn't look very efficient:

     select   date '2002-08-15' as CONTRACT_DISC_DATE
            , current_date as ASOF_DATE

            , case when extract(MONTH from CONTRACT_DISC_DATE) > extract(MONTH from ASOF_DATE)
                   then extract(YEAR from ASOF_DATE)
                   else extract(YEAR from ASOF_DATE) +1
                   end as NXT_YR
             , case
                  when CONTRACT_DISC_DATE < ASOF_DATE then (
                     case
                        when extract(DAY from CONTRACT_DISC_DATE) = 29
                             then cast ( (NXTYR - 1900)*10000
                                       + extract(MONTH from CONTRACT_DISC_DATE)*100
                                       + 28 as date)
                             else cast( (NXTYR - 1900)*10000
                                       + extract(MONTH from CONTRACT_DISC_DATE)*100
                                       + extract(DAY from CONTRACT_DISC_DATE) as date)
                     end)
                  else CONTRACT_DISC_DATE end as CONTRACT_END_DATE

The above works well in every case except when the original contract was on February 29 and the next February is a leap year. It's a very rare thing, but annoying.

I'm also using a "temporary" value NXT_YR. I'll never use that value other than in the formula. Does that have any performance issues?

Thanks again,

Bob Duell
Manager, Systems and Integration
AT&T Wireless Services; CRM Database Marketing

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