Archives of the TeradataForum

Message Posted: Wed, 01 Sep 2004 @ 14:19:24 GMT

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

Hi,

You could try this for size.

Maybe you would be better off creating a table and using the SQL below (or perhaps, someone else has a better method).

Then your queries would be simpler, stats collected and then may perform better.

Good luck

Frank

     select
             calendar_date
             , extract(year from calendar_date) (named yr_of_calendar)
             , (calendar_date - ('1900-01-01' (date))) mod 7 + 1 (named day_of_wk)
             , extract(day from calendar_date) (named day_of_month)
             , extract(month from calendar_date) (named month_of_year)
             , case when month_of_year between 1 and 3 then 1
                                     when month_of_year between 4 and 6 then 2
                                     when month_of_year between 7 and 9 then 3
                                     when month_of_year between 10 and 12 then 4

                     end (named quarter_of_year)

             , day_of_year (named day_of_year)
             , csum(case when day_of_wk = 1 then 1 else 0 end, calendar_date)
                     (named week_of_yr)
     from sys_calendar.calendar c
     where  year_of_calendar in ( 2003, 2004, 2005)

     group by yr_of_calendar;

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