select
tResults.iParentObjectiveID,
tResults.iObjectiveID,
tResults.iQuestionID,
tResults.iRaterID,
tRater.sRater,
tResults.iMIN,
tResults.iMAX,
tResults.iAVG,
tResults.iCount,
tNorms.iAVG as iNorm
---------------------------------------------------------------
-- Results
---------------------------------------------------------------
from
(
---------------------------------------------------------------
-- Group By All +- Self
---------------------------------------------------------------
select
isnull(tQuestion.iParentObjectiveID, -1) as iParentObjectiveID,
isnull(tQuestion.iObjectiveID, -1) as iObjectiveID,
isnull(tQuestion.iQuestionID, -1) as iQuestionID,
isnull(tRater.iRaterID, -1) as iRaterID,
MIN(tResponse.iWeight) as iMIN,
MAX(tResponse.iWeight) as iMAX,
AVG(cast(tResponse.iWeight as float)) as iAVG,
COUNT(tResponse.iTestPersonID) as iCount
from fncCycle_getSurveyQuestions(@iTestID) tQuestion
cross join
(
select
tRater.iRaterID,
tRater.iCycle_EventPropertyID
from fncCycle_getSurveyRaters(@iCycle_EventID, @iHR_UserID, @bRollGroups, @iLanguageID, @iDefaultLanguageID) tRater
where (tRater.bActive = 1 or @bTotalDropped = 1)
and (tRater.sConstant <> 'self' or @bTotalSelf = 1)
and (tRater.sConstant <> 'total')
) tRater
left join
(
select
tTestPerson.iTestPersonID,
tTestPersonAnswer.iQuestionID,
tTestPerson.iCycle_EventPropertyID,
tOption.iWeight
from fncCycle_getSurveyIndividualTestPerson(@iCycle_EventID, @iHR_UserID) tTestPerson
inner join SDCTesting..tTestPersonAnswer tTestPersonAnswer (nolock)
on tTestPerson.iTestPersonID = tTestPersonAnswer.iTestPersonID
inner join SDCTesting..tOption tOption (nolock)
on tTestPersonAnswer.iOptionID = tOption.iOptionID
and tTestPersonAnswer.bSelected = 1
and tOption.iWeight > 0 --Not Applicable
) tResponse
on tQuestion.iQuestionID = tResponse.iQuestionID
and tRater.iCycle_EventPropertyID = tResponse.iCycle_EventPropertyID
where tQuestion.iQuestionTypeID = 0 --Multiple Choice
group by
tQuestion.iParentObjectiveID,
tQuestion.iObjectiveID,
tQuestion.iQuestionID,
tRater.iRaterID
with cube
having
grouping(tQuestion.iParentObjectiveID) = 0
and ( grouping ( tQuestion.iQuestionID ) = 1
or grouping(tQuestion.iObjectiveID) = 0)
---------------------------------------------------------------
-- Group By Self
---------------------------------------------------------------
union
select
isnull(tQuestion.iParentObjectiveID, -1) as iParentObjectiveID,
isnull(tQuestion.iObjectiveID, -1) as iObjectiveID,
isnull(tQuestion.iQuestionID, -1) as iQuestionID,
isnull(tRater.iRaterID, -1) as iRaterID,
MIN(tResponse.iWeight) as iMIN,
MAX(tResponse.iWeight) as iMAX,
AVG(cast(tResponse.iWeight as float)) as iAVG,
COUNT(tResponse.iTestPersonID) as iCount
from fncCycle_getSurveyQuestions(@iTestID) tQuestion
cross join
(
select
tRater.iRaterID,
tRater.iCycle_EventPropertyID
from fncCycle_getSurveyRaters(@iCycle_EventID, @iHR_UserID, @bRollGroups, @iLanguageID, @iDefaultLanguageID) tRater
where (tRater.bActive = 1 or @bTotalDropped = 1)
and (tRater.sConstant = 'self' and @bTotalSelf = 0)
) tRater
left join
(
select
tTestPerson.iTestPersonID,
tTestPersonAnswer.iQuestionID,
tTestPerson.iCycle_EventPropertyID,
tOption.iWeight
from fncCycle_getSurveyIndividualTestPerson(@iCycle_EventID, @iHR_UserID) tTestPerson
inner join SDCTesting..tTestPersonAnswer tTestPersonAnswer (nolock)
on tTestPerson.iTestPersonID = tTestPersonAnswer.iTestPersonID
inner join SDCTesting..tOption tOption (nolock)
on tTestPersonAnswer.iOptionID = tOption.iOptionID
and tTestPersonAnswer.bSelected = 1
and tOption.iWeight > 0 --Not Applicable
) tResponse
on tQuestion.iQuestionID = tResponse.iQuestionID
and tRater.iCycle_EventPropertyID = tResponse.iCycle_EventPropertyID
where tQuestion.iQuestionTypeID = 0 --Multiple Choice
group by
tQuestion.iParentObjectiveID,
tQuestion.iObjectiveID,
tQuestion.iQuestionID,
tRater.iRaterID
with cube
having
grouping(tQuestion.iParentObjectiveID) = 0
and ( grouping ( tQuestion.iQuestionID ) = 1
or grouping(tQuestion.iObjectiveID) = 0)
and grouping(tRater.iRaterID) = 0
) as tResults
left join
(
select distinct
tRater.iRaterID,
tRater.sRater,
tRater.iOrdinal,
tRater.bActive
from fncCycle_getSurveyRaters(@iCycle_EventID, @iHR_UserID, @bRollGroups, @iLanguageID, @iDefaultLanguageID) tRater
) as tRater
on tResults.iRaterID = tRater.iRaterID
where (tRater.bActive = 1 or tRater.iRaterID = -1)
order by
tResults.iParentObjectiveID,
tResults.iObjectiveID,
tResults.iQuestionID,
tRater.iOrdinal,
tRater.iRaterID