Archives of the TeradataForum
Message Posted: Tue, 19 Dec 2006 @ 15:57:26 GMT
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
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|