Crystal 8.5 Calculate Average Days

[brek2]

I am new to Crystal(using Crystal 8.5 with a SQL database). I need to show average days to fill for positions. Positions can be placed on and off hold multiple times. There are two tables as follows:

Table--Positions:
PositionID
DateOpen (date/time)
DateFilled (date/time)

Table--PositionsOnHold:
ID
PositionID
DateOnHold (date/time)
DateOffHold (date/time

I have a left outer join from Positions to PositionsOnHold. I have grouped the report by PositionID.

I have a formula in the group header that works for positions that are never placed on hold--@DaysToFill
DateDiff ("d",{Positions.DateOpen},{Positions.DateFilled})

I have another formula in the detail section to show how many days a position is on hold-- @DaysOnHold
DateDiff ("d",{PositionsOnHold.DateOnHold} ,{PositionsOnHold.DateOffHold} )

I sum @DaysOnHold in the group header to get the total number of days a position is on hold.

The next step is where I have problems. I need something to show if DaysOnHold is null, then subtract DateOpen from DateFilled else subtract total DaysOnHold from DateFilled minus DateOpen. So I tried this which does not work:

If IsNull({@ DaysOnHold}) then {@DaysToFill} else {@DaysToFill} – {@DaysToFill1} - (Sum ({@DaysOnHold}))

I then tried to break it down into this: {@DaysToFill1} - (Sum ({@DaysOnHold})) which the Formula Editor checks out as “No Errors Found” but which returns a strange number.

Any help would be appreciated.