Archives of the TeradataForum

Message Posted: Tue, 19 Dec 2006 @ 17:14:24 GMT

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

Mugdha

Here is a partially worked example for illustration.

Make sure, however, that the last friday stuff is the first condition in the case statement.

Hope this helps,

Frank

     select
     calendar_date
     , case when cal.calendar_date >= lf.last_friday
     then last_day
     /* now add in the other date logic */
     end

     from
     (select
     max(case when day_of_week = 6 then calendar_date else null end) as
     last_friday
     , max(calendar_date) as last_day
     from
     sys_calendar.calendar
     where year_of_calendar = 2006
     ) lf
     cross join sys_calendar.calendar cal
     where extract(year from cal.calendar_date ) = 2006

Frank O'Connor
Information Design & Governance
Enterprise Systems and Technology
Allied Irish Bank

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