erikTsomik
asked on
sql help
I need to calculate the average score fro the survey for the competency and the job role withing that competency
I have the code that calculate the average based on the job role not I need to just get the average for the entire competency.
The calculation should only include values greater than 0
I have the code that calculate the average based on the job role not I need to just get the average for the entire competency.
The calculation should only include values greater than 0
;WITH CTE AS
(
SELECT rateeid ,raterid, compid , Name , VALUE ,compName,surveyID,surveyName
FROM(
SELECT rrm.rateeid ,rrm.raterid, srd.compid , SPR.Name , SRD.VALUE , C.name as compNAme ,SR.surveyID,S.name as surveyName
,COUNT(CASE WHEN SRD.VALUE IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY SPR.Name) cnt
,COUNT(CASE WHEN SRD.VALUE IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY SPR.Name) cnt1
FROM ratee_rater_Map RRM
LEFT JOIN surveyResult SR on SR.surveyID = RRM.surveyID and sr.bodyid = rrm.raterid and sr.targetid = rrm.rateeid
LEFT JOIN surveyResultDetails SRD on SR.resultID = SRD.resultID
LEFT JOIN surveyParticipantsRole SPR on SPR.ID = RRM.relationshipToRatee
LEFT JOIN survey S on S.ID = SR.surveyID
LEFT OUTER JOIN competencies C on C.ID = SRD.compID
WHERE ISNULL(value,0) > = 0
and SR.surveyID = 5
and SR.targetID = 169
and SR.finalized = 1
)K WHERE VALUE IS NULL or cnt = cnt1
)
SELECT rateeid , compid , name , compName,surveyID,surveyName,
ISNULL(CAST ( AVG(abs(value) * 1.) AS DECIMAL(10,2)),0) as avgScore
FROM CTE
GROUP BY surveyID,surveyName,compID,compName,rateeid,name
order by compID,rateeid,name desc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1. sample data
2. expected result
sometimes this is called a "Short, Self Contained, Correct Example" (refer to http://sscce.org/)
It will help a lot if you supplied both sample data (in each referenced table)
and the expected result
https://www.experts-exchange.com/questions/29001073/sql-query-to-calculate-avaerage.html
may have relevant sample data