Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: Generating week dates without sys_calendar
From:   Frank O Connor


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


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