SELECT TRAY_LIST.TL_NO AS TRAY_LIST_NO, TRAY_LIST.SOURCE_LOCATION_NO AS TRAY_LIST_INSTR_LOC_ID, TRAY_LIST.TOTAL AS TOTAL_ITEMS, TRAY_LIST.VERSION AS MAX_VERSION, TRAY_LIST.DESCR AS TRAY_LIST_NAME, TRAY_LIST.CREATED AS LAST_REVISED, ROTATION_TIMES.AVG_ROTATION_DAYS AS ROTATION_DAYS FROM TRAY_LIST_CURRENT_VIEW TRAY_LIST, ( SELECT TL_NO, SUM(TOTAL_ROTATION_DAYS) / SUM(TOTAL_ROTATIONS) AS AVG_ROTATION_DAYS FROM ( SELECT TRAY_LIST.TL_NO, TRAYSET.TRAY_NO, MAX(TRAYSET.ASSM_DT) - MIN(TRAYSET.ASSM_DT) AS TOTAL_ROTATION_DAYS, COUNT(*) - 1 AS TOTAL_ROTATIONS FROM TRAYSET, TRAY_LIST WHERE TRAYSET.TL_ID = TRAY_LIST.TL_ID AND TRAYSET.ASSM_DT > TRAYSET.CREAT_DT AND TRAY_LIST.TL_TYPE = 1 GROUP BY TRAY_LIST.TL_NO, TRAYSET.TRAY_NO HAVING COUNT(*) > 1 ) TRAY_TOTALS GROUP BY TL_NO ) ROTATION_TIMES WHERE TRAY_LIST.TL_NO = ROTATION_TIMES.TL_NO (+)