Archives of the TeradataForum
Message Posted: Sat, 22 May 2004 @ 08:43:06 GMT
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?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|