wht i can understand is u may require to calculate most recurring date or the mid value of extreme dates.simple function of average wont wrk as two dates can not b added as its only DAYS tht can be added to a date. for most recurring date use : SELECT DATE_A FROM ( SELECT DATE_A,COUNT(*) AS COUNT_1 FROM TEMP GROUP BY 1 ) TAB_1 (DATE_A,COUNT_1) QUALIFY RANK(COUNT_1 DESC) = 1 for mid value calc use : select min(date_a) + ((max(date_a) - min(date_a))/2) from temp
Thanks for the reply. We worked around this problem. Since all the dates are in one year, I convert it to Julian Date (YYYYDDD) first, then do a average on the last three digit. then convert it back to normal calendar date. However, if our data set is across years, then I dont know how to deal with it.
SELECT cal.calendar_date AS avg_date FROM sys_calendar.calendar AS cal INNER JOIN ( SELECT AVG(cal.day_of_calendar) AS avg_day FROM sys_calendar.calendar AS cal INNER JOIN tablename AS tbl ON tbl.planting_dt = cal.calendar_date ) AS tmp ON tmp.avg_day = cal.day_of_calendar ;