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