Ru1995
asked on
Having trouble averaging values with a response date in the Select Statement
I need to create a graph for my company that takes the average of all the scores that are taken on a survey for a certain department.
The following is what I have in my query
SELECT typ.deptType
, dept.deptType_id
, AVG(cast(acc.score as float)) AS accuracy
, AVG(cast(tim.score as float)) AS timeliness
, AVG(cast(tude.score as float)) AS attitude
, AVG(cast(access.score as float)) as Accessibility
, Convert(varchar, acc.responseDate, 101) as responseDate
FROM responseDepts dept
INNER JOIN deptTypes typ ON dept.deptType_id = typ.deptType_id
LEFT JOIN responseDeptScores acc ON dept.respDept_id = acc.respDept_id
and(acc.scoreType_id = 1)
and (acc.score != 0)
LEFT JOIN responseDeptScores tim ON dept.respDept_id = tim.respDept_id
and (tim.scoreType_id = 2)
and (tim.score != 0)
LEFT JOIN responseDeptScores tude ON dept.respDept_id = tude.respDept_id
and (tude.scoreType_id = 3)
and (tude.score != 0)
LEFT JOIN responseDeptScores access ON dept.respDept_id = access.respDept_id
and (access.scoreType_id = 4)
and (access.score != 0)
WHERE dept.responseDate >= #createODBCdate(arguments. fromDate)#
AND dept.responseDate < #createODBCdate(arguments. toDate)#
And dept.deptType_id = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.deptID#" >
group by typ.deptType
, dept.deptType_id, acc.responseDate
When I put in the response date in the select statement it rounds all the values to whole numbers. However if I remove the response date I only receive 1 record back in my query with the results being in decimal places. Does anyone know how I can get the average of the department surveyed for each response date?
The following is what I have in my query
SELECT typ.deptType
, dept.deptType_id
, AVG(cast(acc.score as float)) AS accuracy
, AVG(cast(tim.score as float)) AS timeliness
, AVG(cast(tude.score as float)) AS attitude
, AVG(cast(access.score as float)) as Accessibility
, Convert(varchar, acc.responseDate, 101) as responseDate
FROM responseDepts dept
INNER JOIN deptTypes typ ON dept.deptType_id = typ.deptType_id
LEFT JOIN responseDeptScores acc ON dept.respDept_id = acc.respDept_id
and(acc.scoreType_id = 1)
and (acc.score != 0)
LEFT JOIN responseDeptScores tim ON dept.respDept_id = tim.respDept_id
and (tim.scoreType_id = 2)
and (tim.score != 0)
LEFT JOIN responseDeptScores tude ON dept.respDept_id = tude.respDept_id
and (tude.scoreType_id = 3)
and (tude.score != 0)
LEFT JOIN responseDeptScores access ON dept.respDept_id = access.respDept_id
and (access.scoreType_id = 4)
and (access.score != 0)
WHERE dept.responseDate >= #createODBCdate(arguments.
AND dept.responseDate < #createODBCdate(arguments.
And dept.deptType_id = <cfqueryparam cfsqltype="cf_sql_numeric"
group by typ.deptType
, dept.deptType_id, acc.responseDate
When I put in the response date in the select statement it rounds all the values to whole numbers. However if I remove the response date I only receive 1 record back in my query with the results being in decimal places. Does anyone know how I can get the average of the department surveyed for each response date?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER