Cacher is the code snippet organizer for pro developers

--SELECT * FROM dbo.[User] u WHERE u.EMPID LIKE '%21556637%'
DECLARE @userId INT = 3391
DECLARE @waveId INT = 109
DECLARE @assessmentId INT = 43503

--User
SELECT *
FROM dbo.[User] u
WHERE u.UserId = @userId

--Manager
SELECT *
FROM dbo.[User] u
WHERE u.UserId = (
		SELECT u.InternalManagerId
		FROM dbo.[User] u
		WHERE u.UserId = @userId
		)

--Roles
SELECT ucr.UserXCenterXRoleId
	,uc.UserXCenterId
	,r.RoleName
FROM dbo.UserXCenterXRole ucr
INNER JOIN dbo.UserXCenter uc ON ucr.UserXCenterId = uc.UserXCenterId
INNER JOIN dbo.[User] u ON uc.UserId = u.UserId
	AND u.UserId = @userId
LEFT JOIN dbo.ROLE r ON ucr.RoleId = r.RoleId

--Skills
SELECT uccps.Id AS UserCenterXCenterPguSkillId
	,uccps.CenterXPGUXSkillId
	,p.NAME
	,s.SkillName
	,uccps.IsActive
FROM dbo.UserCenterXCenterPguSkill uccps
INNER JOIN dbo.UserXCenter ux ON uccps.UserXCenterId = ux.UserXCenterId
INNER JOIN dbo.[User] u ON ux.UserId = u.UserId
	AND u.UserId = @userId
LEFT JOIN dbo.CenterXPGUXSkill cps ON uccps.CenterXPGUXSkillId = cps.CenterXPGUXSkillId
LEFT JOIN dbo.CenterXPGU cx ON cps.CenterXPGUId = cx.CenterXPGUId
LEFT JOIN dbo.PGU p ON cx.PGUId = p.PGUId
LEFT JOIN dbo.Skill s ON cps.SkillId = s.SkillId

--Assessments
SELECT a.AssessmentId
	,a.AssessmentName
	,a.UserCenterXCenterPguSkillId
	,a.WaveId
	,w.NAME AS WaveName
	,a.TestTakenCount
	,a.NoOfRetest
	,a.AssessmentStatusId
	,asm.NAME AS AssessmentStatus
	,a.StartDate
	,a.CompletedDate
	,a.CreatedDate
	,a.CreatedBy
	,a.ReassignedDate
	,a.ReassignedBy
FROM dbo.Assessment a
INNER JOIN dbo.UserCenterXCenterPguSkill uccps ON a.UserCenterXCenterPguSkillId = uccps.Id
INNER JOIN dbo.UserXCenter ux ON uccps.UserXCenterId = ux.UserXCenterId
INNER JOIN dbo.[User] u ON ux.UserId = u.UserId
	AND u.UserId = @userId
LEFT JOIN dbo.CenterXPGUXSkill cps ON uccps.CenterXPGUXSkillId = cps.CenterXPGUXSkillId
LEFT JOIN dbo.CenterXPGU cx ON cps.CenterXPGUId = cx.CenterXPGUId
LEFT JOIN dbo.PGU p ON cx.PGUId = p.PGUId
LEFT JOIN dbo.Skill s ON cps.SkillId = s.SkillId
LEFT JOIN dbo.Wave w ON a.WaveId = w.Id
LEFT JOIN dbo.AssessmentStatusMaster asm ON a.AssessmentStatusId = asm.Id
WHERE (
		@waveId = - 1
		OR a.WaveId = @waveId
		)

--User Assessment Questions
SELECT uaq.UserAssessmentQuestionId
	,uaq.UserId
	,uaq.AssessmentId
	,a.AssessmentName
	,uaq.CenterXPGUXSkillId
	,uaq.ActivityId
	,uaq.QuestionId
	,uaq.Answer
	,uaq.TimeLeft
	,uaq.STATUS
	,uaq.IsActive
	,uaq.CreatedDate
	,uaq.ModifiedDate
	,uaq.IsCorrect
FROM dbo.UserAssessmentQuestion uaq
INNER JOIN dbo.Assessment a ON uaq.AssessmentId = a.AssessmentId
WHERE uaq.AssessmentId = @assessmentId

--Rating
SELECT tr.*
FROM dbo.TCIAssessmentResult tr
WHERE tr.AssessmentId = @assessmentId
	----Re assign the assessment
	--DECLARE @aId int= 23731
	--EXEC dbo.USP_ReassignAssesstment @assessmentId = @aId,@createdBy = 1483
	--UPDATE assessment SET TestTakenCount = 1 WHERE AssessmentId = @aId
	/*
	DECLARE @aId int = 43503
	UPDATE dbo.Assessment SET AssessmentStatusId = 5,
	StartDate	= (SELECT min(uaq.CreatedDate) FROM dbo.UserAssessmentQuestion uaq WHERE uaq.AssessmentId = @aId), 
	CompletedDate = (SELECT max(uaq.CreatedDate) FROM dbo.UserAssessmentQuestion uaq WHERE uaq.AssessmentId = @aId) WHERE AssessmentId = @aId
	*/