OTOMH --

1. If you can use the WHERE clause to filter out code of not "R", that's preferred.

2. If not, I think if you replace the 0.0 with .NULL. as below, that the null records will not be included in the calculation (I think).

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

Jay

>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