Archives of the TeradataForum

Message Posted: Tue, 19 Dec 2006 @ 15:57:26 GMT

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

Hi,

My requirement is to populate any date after the last Friday of the year as YYYY/12/31.

The date that needs to be compared with the date on last Friday of the year is manufactured_date from table Qs_serial_product.

I have to include above req in my query below.

     insert into temp1
     (
     product_id,
     completion_date,
     organisation_id,
     quanity
     )
     sel
     a.product_id,
     case
          when b.day_of_week='1'
             then a.manufactured_date+5
          when b.day_of_week='2'
             then a.manufactured_date+4
          when b.day_of_week='3'
             then a.manufactured_date+3
          when b.day_of_week='4'
             then a.manufactured_date+2
          when b.day_of_week='5'
             then a.manufactured_date+1
          when b.day_of_week='6'
             then a.manufactured_date
          else a.manufactured_date+6
     END
     ,
     a.plant_code,
     count(a.serial_nbr)
     from
     qs_serial_product a left outer join sys_calendar.calendar b
     on a.manufactured_date=b.calendar_date
     where a.manufactured_date >=
     CAST(
     TRIM(extract(YEAR FROM ADD_MONTHS(current_date, -5)))||
     TRIM(extract(MONTH FROM ADD_MONTHS(current_date, -5))  (FORMAT '9(2)'))
     ||
     '01' AS DATE FORMAT 'YYYYMMDD')
     group by 1,2,3
     order by 2 desc

This query gets the previous 6 months data from the current date and populates the completion_date column with the date on Friday of a week.

The way I got the date on last Friday of a year is as follows:

     sel MAX(calendar_date) from sys_calendar.calendar
     where month_of_year=12
     and day_of_week=6
     and year_of_calendar =extract( year from current_date)

Can any one help me on this.

Thanks in advance

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