Archives of the TeradataForum

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

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

<-- Anonymously Posted: Friday, October 08, 2004 14:56 -->

Based on work from various authors on the forum I was able to piece together the following SQL:

     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
               ,calendar_date enddate

            FROM   sys_calendar.calendar
            QUALIFY RANK() OVER ( PARTITION BY month_of_year
            ORDER BY weekday_of_month desc) = 1
            where year_of_calendar = 2004  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

This SQL constructs the 2004 calendar as required, and if the condition is changed to year_of_calendar = 2005, that year is also constructed properly. However, if I change the year_of_calendar condition to BETWEEN 2004 and 2005, there are missing rows. The number of missing rows per year increases with the number of years in the range, up to 2008. Can anyone explain what is going on, and maybe suggest a solution that would work for a range of years?

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