Archives of the TeradataForum

Message Posted: Tue, 19 Dec 2006 @ 23:22:57 GMT

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

<-- Anonymously Posted: Tuesday, December 19, 2006 18:10 -->

Mugdha,

See if the following SQL will do what you want if you mean to have report on last 5 complete months and whatever current month data:

     insert into temp1

     (
     product_id,
     completion_date,
     organisation_id,
     quanity
     )

     sel
     a.product_id,
     case
     when a.manufactured_date > b.last_friday_of_the_year
     then d.last_day_of_the_year
     else c.friday_of_manufactured_week
     END
     ,a.plant_code
     ,count(a.serial_nbr)

     from

     qs_serial_product a
     left outer join
     /* last Friday of each year to be join to table a on manufactured year
     */
     (sel max(calendar_date) as last_friday_of_the_year
     ,year_of_calendar
     from sys_calendar.calendar
     where day_of_week=6
     group by 2) b
     on
     extract(year from a.manufactured_date)=b.year_of_calendar

     left outer join
     /* last Friday of each week to be join to table a on manufactured week
     */
     (sel
     calendar_date as friday_of_manufactured_week
     ,year_of_calendar
     ,month_of_year
     ,week_of_month
     from sys_calendar.calendar
     where day_of_week=6) c
     on
     extract(year from a.manufactured_date)=c.year_of_calendar
     and extract(month from a.manufactured_date=c.month_0f_year
     and c.week_of_month=
     (select week_of_month from sys_calendar.calendar
     where calendar_date=a.manufactured_date)

     left outer join
     /* last day of each year to be join to table a on manufactured year */
     (sel max(calendar_date) as last_day_of_the_year
     ,year_of_calendar
     from sys_calendar.calendar
     group by 2) d
     on
     extract(year from a.manufactured_date)=d.year_of_calendar

     where a.manufactured_date between
     /*  first day of 5 months prior to current month */
     (sel min(calendar_date)
     from sys_calendar.calendar
     where year_of_calendar=extract(year from add_months(date,-5))
     and month_of_year=extract(month from add_months(date,-5)))
     and
     /*  last day of current month */
     (sel max(calendar_date)
     from sys_calendar.calendar
     where year_of_calendar=extract(year from date)
     and month_of_year=extract(month from date))

     group by 1,2,3
     order by 2 desc

     ;

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