Archives of the TeradataForum

Message Posted: Sun, 10 Oct 2004 @ 11:42:27 GMT

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

<-- Anonymously Posted: Saturday, October 09, 2004 12:18 -->

Although not very elegant, the following code works for the needed date range, without any gaps:

     SELECT
        T2.year_of_calendar
        ,T2.month_of_year
        ,CASE
            WHEN day_of_week = 6 and week_of_month = 0
                 THEN  calendar_date - 6
            WHEN day_of_week = 6 and week_of_month = 1 and day_of_month BETWEEN 6 and 7
                 THEN  calendar_date - 6
         END startdate
        ,T1.enddate

     FROM ( SELECT
               year_of_calendar
               ,month_of_year
               ,CASE
                   WHEN week_of_month = 4 and weekday_of_month = 5
                      THEN  calendar_date
                   WHEN week_of_month = 3 and weekday_of_month = 4
                        and month_of_year IN (1,3,5,7,8,10,12) and day_of_month >= 25
                      THEN  calendar_date
                   WHEN  week_of_month = 4 and weekday_of_month = 4
                         and month_of_year IN (1,3,5,7,8,10,12) and day_of_month >= 27
                      THEN  calendar_date
                   WHEN  week_of_month = 3 and weekday_of_month = 4
                         and month_of_year IN (4,6,9,11) and day_of_month BETWEEN 24 and 26
                      THEN  calendar_date
                   WHEN  week_of_month = 4 and weekday_of_month = 4
                         and month_of_year IN (4,6,9,11) and day_of_month >= 27
                      THEN  calendar_date
                   WHEN day_of_week = 6 and week_of_month = 3 and weekday_of_month = 4
                        and month_of_year IN (2) and year_of_calendar MOD 4 = 0 and day_of_month >= 24
                      THEN  calendar_date
                   WHEN day_of_week = 6 and week_of_month = 4 and weekday_of_month = 4
                        and month_of_year IN (2) and year_of_calendar MOD 4 = 0 and day_of_month >= 27
                      THEN  calendar_date
                   WHEN day_of_week = 6 and week_of_month = 3 and weekday_of_month = 4
                        and month_of_year IN (2) and year_of_calendar MOD 4 <> 0 and day_of_month >= 22
                      THEN  calendar_date
                   WHEN day_of_week = 6 and week_of_month = 4 and weekday_of_month = 4
                        and month_of_year IN (2) and year_of_calendar MOD 4 <> 0 and day_of_month >= 27
                      THEN  calendar_date
                   END enddate

            FROM sys_calendar.calendar
               where year_of_calendar BETWEEN 2004 and 2007 and enddate IS NOT NULL and
               day_of_week = 6 ) T1, sys_calendar.calendar T2

     WHERE startdate is not null
     and T1.year_of_calendar= T2.year_of_calendar
     and T1.month_of_year  = T2.month_of_year
     order by 1,2

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