Avg, Min & Max of two date fields in a Table

i have a table T_EPISODE that has two fields referral date (D_REF) and assessment date (A_DATE)

i need to get the average, min & max time between the referral and assessment dates where the assessment date falls within a given period (dteFromDate & dteToDate) and where assessment date is not null

but im a little bit stuck on how to do it. i know about the MIN & MAX functions but they only work one field at a time. do i need to do a cursor to get all the date differences then work off these for MIN, MAX & AVG?

any help would be greatly appreciated.

Cheers,
Craig

You take min, max, avg of a expression. An expression can be a column or anything else that qualifies as an expression.
select min(A_DATE - D_REF) from T_EPISODE
where A_DATE between dteFromDate and dteToDate

|||

Hi andreas,

did you have any idea how to solve this problem for me?

i need to use the data to get the highest average mark?

now i have a table that contain the score for some question.

i need to group it into daily basic to get each day average score. and then i want to get the highest score over several day. anyway can help me to do it?

regards

terence chua

|||

Hallo,

created a small scenario to hopefully show how to do what you seem to want to do.

CREATE TABLE [dbo].[Scores](
[nScore] [int] NOT NULL,
[dtDate] [datetime] NOT NULL
) ON [PRIMARY]

I filled the table with some scores for various dates, I used a date in this month.

To get the average for a period in time I used this query
select avg(nScore) as 'Daily Average', dtDate
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'
group by dtDate
order by dtDate

where clause determines what period.

To get the highest score in a period
select max(nScore) as 'Top Score'
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'

I hope this helps, otherwise you might explain more detailed what you are trying to.

|||

Hi,

thank your help. i think you misunderstand my question. anyway i get my solution already. sorry to reply late. cos my company closing for CNY.

i not joining both function for avg() and max(). but i just use avg() to select the data i need. then i use the sort and filter in the object(table, chart, text box and so on). to select the top N record or bottom N record then i can get the maximun and minimun record already.

regards

terence chua