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