Chandrashekar
3/22/2017 - 10:03 AM

Report Wave Summary Counts

Report Wave Summary Counts

-----------------------------------------------------------------------------Oproach 01-------------------------------------------------------------
DECLARE @tblWave TABLE (
	Id INT IDENTITY
	,WaveId INT
	)

INSERT INTO @tblWave (WaveId)
SELECT  w.Id
FROM dbo.Wave w
WHERE w.IsActive = 1
ORDER BY w.FromDate

DECLARE @i INT = 1
DECLARE @rCount INT = 0
DECLARE @LWaveId INT = 0

SELECT @rCount = count(tw.Id)
FROM @tblWave tw

DECLARE @uniqueEngineersCount INT
DECLARE @assessmentCount INT
DECLARE @averageRating FLOAT
DECLARE @uniqueQuestionCount INT

WHILE (@i < = @rCount)
BEGIN
	SELECT @LWaveId = tw.WaveId
	FROM @tblWave tw
	WHERE tw.Id = @i

	PRINT ('************************************************************************************************************************************************************')

	--------------------Wave Info---------------------------------------
	SELECT convert(NVARCHAR(50), w.NAME) WaveName
		,convert(NVARCHAR(50), w.FromDate) AS FromDate
		,w.ToDate
	FROM dbo.Wave w
	WHERE w.Id = @LWaveId

	-------------------Wave Skills----------------------------------------
	SELECT convert(NVARCHAR(50), s.SkillName) AS SkillNames
	FROM dbo.WaveXCenterPguSkill wxps
	INNER JOIN dbo.Wave w ON wxps.WaveId = w.Id
		AND w.Id = @LWaveId
	INNER JOIN dbo.CenterXPGUXSkill cx ON wxps.CenterPguSkillId = cx.CenterXPGUXSkillId
	INNER JOIN dbo.Skill s ON cx.SkillId = s.SkillId

	-------------------Wave Facilities----------------------------------------
	SELECT convert(NVARCHAR(50), fm.NAME) AS FacilityName
	FROM dbo.WaveXFacility wx
	INNER JOIN dbo.Wave w ON wx.WaveId = w.Id
		AND w.Id = @LWaveId
	INNER JOIN dbo.FacilityMaster fm ON wx.FacilityId = fm.Id

	---------------------Wave Unique Engineers Participate---------------------
	SELECT @uniqueEngineersCount = count(DISTINCT u.EmpId)
	FROM dbo.Assessment a
	INNER JOIN dbo.UserCenterXCenterPguSkill ucxps ON a.UserCenterXCenterPguSkillId = ucxps.Id
	INNER JOIN dbo.UserXCenter ux ON ucxps.UserXCenterId = ux.UserXCenterId
	INNER JOIN dbo.[User] u ON ux.UserId = u.UserId
	WHERE a.WaveId = @LWaveId

	------------------Assessments Count----------------------------------------
	SELECT @assessmentCount = count(a.AssessmentId)
	FROM dbo.Assessment a
	WHERE a.WaveId = @LWaveId
		AND a.IsActive = 1

	------------------Avg Rating----------------------------------------
	SELECT @averageRating = avg(convert(FLOAT, tr.Rating))
	FROM dbo.TCIAssessmentResult tr
	INNER JOIN dbo.Assessment a ON tr.AssessmentId = a.AssessmentId
	WHERE a.WaveId = @LWaveId

	--------------------Unique Question Count---------------------------
	SELECT @uniqueQuestionCount = count(DISTINCT uaq.QuestionId)
	FROM dbo.UserAssessmentQuestion uaq
	INNER JOIN dbo.Assessment a ON uaq.AssessmentId = a.AssessmentId
	WHERE a.WaveId = @LWaveId

	SELECT @assessmentCount AS AssessmentCount
		,@averageRating AS AverageRating
		,@uniqueEngineersCount AS UniqueEngineersCount
		,@uniqueQuestionCount AS UniqueQuestionCount

	SET @i = @i + 1;
	SET @uniqueEngineersCount = 0
	SET @assessmentCount = 0
	SET @averageRating = 0
	SET @uniqueQuestionCount = 0
END
-----------------------------------------------------------------------------Oproach 02-------------------------------------------------------------

DECLARE @tblWave TABLE (
	Id INT IDENTITY
	,WaveId INT
	)

INSERT INTO @tblWave (WaveId)
SELECT w.Id
FROM dbo.Wave w
WHERE w.IsActive = 1
ORDER BY w.FromDate

DECLARE @i INT = 1
DECLARE @rCount INT = 0
DECLARE @LWaveId INT = 0

SELECT @rCount = count(tw.Id)
FROM @tblWave tw

DECLARE @waveName NVARCHAR(50)
DECLARE @waveStartDate DATE
DECLARE @uniqueEngineersCount INT
DECLARE @assessmentCount INT
DECLARE @skillCount INT
DECLARE @uniqueQuestionCount INT
DECLARE @averageRating FLOAT
DECLARE @facilityCount INT

WHILE (@i < = @rCount)
BEGIN
	SELECT @LWaveId = tw.WaveId
	FROM @tblWave tw
	WHERE tw.Id = @i

	PRINT ('************************************************************************************************************************************************************')

	--------------------Wave Info---------------------------------------
	SELECT @waveName = w.NAME
		,@waveStartDate = w.FromDate
	FROM dbo.Wave w
	WHERE w.Id = @LWaveId

	---------------------Wave Unique Engineers Participate---------------------
	SELECT @uniqueEngineersCount = count(DISTINCT u.EmpId)
	FROM dbo.Assessment a
	INNER JOIN dbo.UserCenterXCenterPguSkill ucxps ON a.UserCenterXCenterPguSkillId = ucxps.Id
	INNER JOIN dbo.UserXCenter ux ON ucxps.UserXCenterId = ux.UserXCenterId
	INNER JOIN dbo.[User] u ON ux.UserId = u.UserId
	WHERE a.WaveId = @LWaveId

	------------------Assessments Count----------------------------------------
	SELECT @assessmentCount = count(a.AssessmentId)
	FROM dbo.Assessment a
	WHERE a.WaveId = @LWaveId
		AND a.IsActive = 1

	-------------------Wave Skills----------------------------------------
	SELECT @skillCount = count(DISTINCT wxps.CenterPguSkillId)
	FROM dbo.WaveXCenterPguSkill wxps
	INNER JOIN dbo.Wave w ON wxps.WaveId = w.Id
		AND w.Id = @LWaveId
	INNER JOIN dbo.CenterXPGUXSkill cx ON wxps.CenterPguSkillId = cx.CenterXPGUXSkillId
	INNER JOIN dbo.Skill s ON cx.SkillId = s.SkillId

	--------------------Unique Question Count---------------------------
	SELECT @uniqueQuestionCount = count(DISTINCT uaq.QuestionId)
	FROM dbo.UserAssessmentQuestion uaq
	INNER JOIN dbo.Assessment a ON uaq.AssessmentId = a.AssessmentId
	WHERE a.WaveId = @LWaveId

	------------------Avg Rating----------------------------------------
	SELECT @averageRating = avg(convert(FLOAT, tr.Rating))
	FROM dbo.TCIAssessmentResult tr
	INNER JOIN dbo.Assessment a ON tr.AssessmentId = a.AssessmentId
	WHERE a.WaveId = @LWaveId

	-------------------Wave Facilities----------------------------------------
	SELECT @facilityCount = count(DISTINCT wx.FacilityId)
	FROM dbo.WaveXFacility wx
	INNER JOIN dbo.Wave w ON wx.WaveId = w.Id
		AND w.Id = @LWaveId
	INNER JOIN dbo.FacilityMaster fm ON wx.FacilityId = fm.Id

	--SELECT @waveName
	--	,@waveStartDate
	--	,@uniqueEngineersCount
	--	,@assessmentCount
	--	,@skillCount
	--	,@uniqueQuestionCount
	--	,@averageRating
	--	,@facilityCount
	PRINT 'WaveStartDate: ' + convert(NVARCHAR(50), @waveStartDate)
	PRINT 'TCI Wave Name: ' + @waveName	
	PRINT 'Participation: ' + convert(NVARCHAR(50), @uniqueEngineersCount)
	PRINT 'Assessments  : ' + convert(NVARCHAR(50), @assessmentCount)
	PRINT 'Skills       : ' + convert(NVARCHAR(50), @skillCount)
	PRINT 'Question     : ' + convert(NVARCHAR(50), @uniqueQuestionCount)
	PRINT 'AvgScore     : ' + convert(NVARCHAR(50), @averageRating)
	PRINT 'Centers      : ' + convert(NVARCHAR(50), @facilityCount)

	--SELECT @assessmentCount AS AssessmentCount
	--	,@averageRating AS AverageRating
	--	,@uniqueEngineersCount AS UniqueEngineersCount
	--	,@uniqueQuestionCount AS UniqueQuestionCount
	SET @i = @i + 1;
	--
	SET @waveName = ''
	SET @waveStartDate = NULL
	SET @uniqueEngineersCount = 0
	SET @assessmentCount = 0
	SET @skillCount = 0
	SET @uniqueQuestionCount = 0
	SET @averageRating = 0.0
	SET @facilityCount = 0
END
-----------------------------------------------------------------------------Oproach 03-------------------------------------------------------------

SELECT w.NAME AS WaveName
	,convert(date, w.FromDate) AS WaveDate
	,ParticipationCount = (
		SELECT count(DISTINCT u.EmpId)
		FROM dbo.Assessment a
		INNER JOIN dbo.UserCenterXCenterPguSkill ucxps ON a.UserCenterXCenterPguSkillId = ucxps.Id
		INNER JOIN dbo.UserXCenter ux ON ucxps.UserXCenterId = ux.UserXCenterId
		INNER JOIN dbo.[User] u ON ux.UserId = u.UserId
		WHERE a.WaveId = w.Id
		)
	,AssessmentCount = (
		SELECT count(a.AssessmentId)
		FROM dbo.Assessment a
		WHERE a.WaveId = w.Id
			AND a.IsActive = 1
		)
	,SkillCount = (
		SELECT count(DISTINCT wxps.CenterPguSkillId)
		FROM dbo.WaveXCenterPguSkill wxps
		WHERE wxps.WaveId = w.Id
		)
	,QuestionCount = (
		SELECT count(DISTINCT uaq.QuestionId)
		FROM dbo.UserAssessmentQuestion uaq
		INNER JOIN dbo.Assessment a ON uaq.AssessmentId = a.AssessmentId
		WHERE a.WaveId = w.Id
		)
	,AvgScore = (
		SELECT round( avg(convert(FLOAT, tr.Rating)),2)
		FROM dbo.TCIAssessmentResult tr
		INNER JOIN dbo.Assessment a ON tr.AssessmentId = a.AssessmentId
		WHERE a.WaveId = w.Id
		)
	,FacilityCount = (
		SELECT count(DISTINCT wx.FacilityId)
		FROM dbo.WaveXFacility wx
		WHERE wx.WaveId = w.Id
		)
FROM dbo.Wave w
WHERE w.IsActive = 1
ORDER BY w.FromDate