I am trying to arive at a summary data set for work performed in our facility. Ulitmately what i would like to have is an Avg Manhours, Turn Around Time, Average Material Cost, and Average Material Cost per Day (=Avg Mat. Cost/Turn around Time). I would like to do this without having to create multiple QVDs which then need to be loaded and calcuated upon in order to eventually arrive at this data set. Is this possible? can it be done with a loop? I have pasted my script below and it is not running because nested aggregation is not allowed. That is because i am trying to Sum the Averages to arrive at a total material cost average per WO_DATA_CODE. (SUM(AVG(SUM_C_COST)+AVG(SUM_S_COST)+AVG(SUM_R_COST)) as MATERIAL_COST. Any help in performing the calculation in bold and underlined in the script below would be appreciated.
Thanks,
B
LOAD
WO_ORG_CODE&'_'&WO_GROUP_PRIMARY&'_'&WK_TYPE AS WO_DATA_CODE,
WO_GROUP_PRIMARY, WO_ORG_CODE, ROUND(AVG(HRS),.1) AS AVG_HRS,
AVG(HRS),
AVG(SUM_C_COST),
AVG(SUM_S_COST),
AVG(SUM_R_COST),
SUM(AVG(SUM_C_COST)+AVG(SUM_S_COST)+AVG(SUM_R_COST)) as MATERIAL_COST,
IF(ROUND(AVG([DAYS WORKED]),1)<1,1,ROUND(AVG([DAYS WORKED]),1)) AS TAT,
AVG([DAYS WORKED])
FROM .\QVD\ARA_Completed_Work_Orders.qvd (qvd)
WHERE
WO_POSITION_CODE ='SHOP'
and [LAST WK YEAR] > YEAR(ReloadTime())-4
GROUP BY
WO_ORG_CODE&'_'&WO_GROUP_PRIMARY&'_'&WK_TYPE,
WO_GROUP_PRIMARY, WO_ORG_CODE;
