|
|
|
Junior Member
 
Group: Forum Members
Last Login: 8/25/2008 5:46:43 PM
Posts: 23,
Visits: 73
|
|
when I am trying to use the average funtion on a date field, please see the following sql.
select avg(planting_dt) from tablename;
I encountered following error message:
2616: Numeric overflow occurred during computation.
Is there any possible way to do average on a date filed which has a lot of values.
thanks,
Ivy
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/11/2008 5:44:44 PM
Posts: 2,
Visits: 9
|
|
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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/25/2008 5:46:43 PM
Posts: 23,
Visits: 73
|
|
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.
Ivy
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 6/27/2008 7:19:28 AM
Posts: 42,
Visits: 54
|
|
You could use CALENDAR view :
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
;
|
|
|
|