Archives of the TeradataForum

Message Posted: Wed, 01 Sep 2004 @ 14:30:58 GMT

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

Madhukar

The following is not intended to be the complete and end all solution to your question. However, it is intended to give you the basis to build what you need. The key to the solution is that Jan 1, 1900 was a Monday; your week starts on a Monday. The week of the year column is there like in the system calendar - a full week (ie week 1) starts on a Monday.

You can build the 7 dates by getting the Monday you are seeking and then selecting 6 more columns adding 1 to each respective date.

here is the SQL:

     sel  *
     from (sel case when ((date/10000*10000+101(date))-(101(date))) mod 7 > 0 then 0 else 1 end as Jan_week
                       ,((date/10000*10000+101(date))-(101(date))) mod 7 as day_num
                        ,case ((date/10000*10000+101(date))-(101(date))) mod 7 when 0 then 'Monday'
                                                                               when 1 then 'Tuesday'
                                                                               when 2 then 'Wednesday'
                                                                               when 3 then 'Thursday'
                                                                               when 4 then 'Friday'
                                                                               when 5 then 'Saturday'
                                                                                 else 'Sunday' end as Day_of_week
                                ,10*7+day_num as week10_days_of_yr
                                ,(1040101(date))+week10_days_of_yr as yr_date
     ) First_week_tbl

If you have any questions, please feel free to ask.

Regards,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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