>I have a file that tracks date ranges for contracts. When the contract is initiated a record is created with a bgndate. When the contract is terminated that same record is updated with a enddate and a code of "R".
>
>I have a select statement on this file which does quite a bit of stuff..but pertinent fields would be #contracts initiated, #contracts terminated and finally an average length of contract.
>
>I am struggling with how to get the correct average. The following line(which is a portion of my select statement), will skew the average because it includes those that have not terminated.
>
>

>avg(iif(scinvrnt.inr_dunn = "R", (scinvrnt.inr_enddate - scinvrnt.inr_bgndate)/30.00, 0.00)) as nAvgMonths
>

>
>Basically I would like a way the avg() command to only take into account those records that have a dunn of "R"
>
>Thanks in advance,
>Paul

Try this:

SUM(iif(scinvrnt.inr_dunn = "R", (scinvrnt.inr_enddate - scinvrnt.inr_bgndate)/30.00, 0.00)) / SUM(iif(scinvrnt.inr_dunn = "R",1,0))