Cacher is the code snippet organizer for pro developers

DECLARE @centerId INT = 1
DECLARE @loginUserId INT = 4051
DECLARE @tblRptSource TABLE (
	CenterId INT
	,WaveId INT
	,MgrUserId INT
	,EngUserId INT
	,CenterXPGUXSkillId INT
	,IsSkillAssigned BIT
	,AssessmentId INT
	,AssessmentStatusId INT
	,CreatedDate VARCHAR(250)
	,StartDate VARCHAR(250)
	,CompletedDate VARCHAR(250)
	,TimeTaken VARCHAR(10)
	,TestTakenCount VARCHAR(10)
	,NoOfRetest VARCHAR(10)
	,Rating VARCHAR(10)
	,MgrAtAssessmentUserId INT
	,PrimaryRoleAtAssessment VARCHAR(250)
	,CenterSecondaryRoleIdAtAssessment INT
	,FacilityIdAtAssessment INT
	,ServiceTowerAtAssessment VARCHAR(250)
	,RegionAtAssessment VARCHAR(250)
	,CountryAtAssessment VARCHAR(250)
	)

INSERT INTO @tblRptSource (
	CenterId
	,WaveId
	,MgrUserId
	,EngUserId
	,CenterXPGUXSkillId
	,IsSkillAssigned
	,AssessmentId
	,AssessmentStatusId
	,CreatedDate
	,StartDate
	,CompletedDate
	,TimeTaken
	,TestTakenCount
	,NoOfRetest
	,Rating
	,MgrAtAssessmentUserId
	,PrimaryRoleAtAssessment
	,CenterSecondaryRoleIdAtAssessment
	,FacilityIdAtAssessment
	,ServiceTowerAtAssessment
	,RegionAtAssessment
	,CountryAtAssessment
	)
SELECT uc.CenterId
	,a.WaveId
	,u.InternalManagerId AS MgrUserId
	,uc.UserId AS EngUserId
	,cps.CenterXPGUXSkillId
	,CASE 
		WHEN ucxcps.IsActive = 1
			THEN 1
		ELSE 0
		END AS IsSkillAssigned
	,a.AssessmentId
	,a.AssessmentStatusId
	,CASE 
		WHEN a.CreatedDate IS NULL
			THEN ''
		ELSE convert(VARCHAR(20), a.CreatedDate, 120)
		END AS CreatedDate
	,CASE 
		WHEN a.StartDate IS NULL
			THEN ''
		ELSE convert(VARCHAR(20), a.StartDate, 120)
		END AS StartDate
	,CASE 
		WHEN a.CompletedDate IS NULL
			THEN ''
		ELSE convert(VARCHAR(20), a.CompletedDate, 120)
		END AS CompletedDate
	,CASE 
		WHEN a.CompletedDate IS NULL
			OR a.StartDate IS NULL
			THEN ''
		ELSE CONVERT(VARCHAR(10), CAST(ROUND(DATEDIFF(second, a.StartDate, a.CompletedDate) / 60.0, 2) AS NUMERIC(36, 2)))
		END AS TimeTaken
	,a.TestTakenCount
	,a.NoOfRetest
	,CASE 
		WHEN tr.Rating IS NULL
			THEN ''
		ELSE convert(VARCHAR(100), tr.Rating)
		END AS Rating
	,a.MgrUserId AS MgrAtAssessmentUserId --Manager at the time of taking assessment, not the current Manager          
	,a.PrimaryRole
	,a.CenterSecondaryRoleId
	,a.FacilityId
	,a.ServiceTower
	,a.Region
	,a.Country
FROM dbo.Assessment a WITH (NOLOCK)
INNER JOIN dbo.UserCenterXCenterPguSkill ucxcps WITH (NOLOCK) ON ucxcps.Id = a.UserCenterXCenterPguSkillId --Get Inactive skills as well         
INNER JOIN dbo.UserXCenter uc WITH (NOLOCK) ON uc.UserXCenterId = ucxcps.UserXCenterId
	AND uc.IsActive = 1
	AND uc.CenterId = @centerId --Filter Center          
INNER JOIN dbo.[User] u WITH (NOLOCK) ON u.UserId = uc.UserId
	AND u.IsActive = 1
INNER JOIN dbo.CenterXPGUXSkill cps WITH (NOLOCK) ON cps.CenterXPGUXSkillId = ucxcps.CenterXPGUXSkillId
	AND cps.IsActive = 1
LEFT JOIN dbo.TCIAssessmentResult tr WITH (NOLOCK) ON tr.AssessmentId = a.AssessmentId
WHERE a.IsActive = 1
--AND ISNULL(a.WaveId, - 1) = @waveId --Filter Wave              
ORDER BY a.WaveId
	,a.MgrUserId
	,uc.UserId
	,cps.CenterXPGUId
	,cps.CenterXPGUXSkillId;
WITH CTE AS (
		SELECT trs.CenterId
			,trs.WaveId
			,trs.MgrUserId
			,trs.EngUserId
			,dbo.UFN_GetFacilityIdByUserId(trs.EngUserId) AS FacilityId
			,dbo.UFN_GetCenterSecondaryRoleByUserId(trs.EngUserId, @centerId) AS CenterSecondaryRoleId
			,trs.CenterXPGUXSkillId
			,trs.IsSkillAssigned
			,trs.AssessmentId
			,trs.AssessmentStatusId
			,trs.CreatedDate
			,trs.StartDate
			,trs.CompletedDate
			,trs.TimeTaken
			,trs.TestTakenCount
			,trs.NoOfRetest
			,trs.Rating
			,trs.MgrAtAssessmentUserId
			,trs.PrimaryRoleAtAssessment
			,trs.CenterSecondaryRoleIdAtAssessment
			,trs.FacilityIdAtAssessment
			,trs.ServiceTowerAtAssessment
			,trs.RegionAtAssessment
			,trs.CountryAtAssessment
		FROM @tblRptSource trs
		INNER JOIN dbo.[User] u ON trs.EngUserId = u.UserId
		)

--Select Here                    
SELECT rpt.CenterId
	,c.CenterName AS Center
	,ISNULL(rpt.WaveId, - 1) AS WaveId
	,ISNULL(w.NAME, 'Dynamic Assessment') AS Wave
	,ISNULL(CONVERT(VARCHAR(50), rpt.MgrUserId), '') AS MgrUserId
	,ISNULL(mgru.UserName, '') AS ManagerName
	,ISNULL(rpt.EngUserId, - 1) AS EngUserId
	,CASE 
		WHEN NOT EXISTS (
				SELECT 1
				FROM UFN_GetEmployeesByMgrId(@loginUserId, @centerId) tb
				WHERE tb.UserId = rpt.EngUserId
				)
			AND UPPER(LTRIM(RTRIM(engu.Country))) = UPPER(LTRIM(RTRIM('Germany')))
			THEN '*****'
		ELSE ISNULL(engu.UserName, '')
		END AS EngName
	,CASE 
		WHEN NOT EXISTS (
				SELECT 1
				FROM UFN_GetEmployeesByMgrId(@loginUserId, @centerId) tb
				WHERE tb.UserId = rpt.EngUserId
				)
			AND UPPER(LTRIM(RTRIM(engu.Country))) = UPPER(LTRIM(RTRIM('Germany')))
			THEN '*****'
		ELSE ISNULL(engu.EmpId, '')
		END AS EmpId
	,engu.PrimaryRole PrimaryRole
	,engu.SecondaryRole
	,engu.Region
	,engu.FacilityDesc
	,engu.ServiceTower
	,engu.Country
	,ISNULL(cp.CenterXPGUId, '') AS CenterXPGUId
	,ISNULL(p.NAME, '') AS Pgu
	,ISNULL(rpt.CenterXPGUXSkillId, - 1) AS CenterPguSkillId
	,ISNULL(s.SkillName, '') AS SkillName
	,CASE 
		WHEN ISNULL(rpt.IsSkillAssigned, 0) = 1
			THEN 'YES'
		ELSE 'NO'
		END AS IsSkillAssigned
	,ISNULL(CONVERT(NVARCHAR(10), rpt.AssessmentId), '') AS AssessmentId
	,ISNULL(CONVERT(NVARCHAR(10), rpt.AssessmentStatusId), '') AS AssessmentStatusId
	,ISNULL(asm.NAME, 'No Test') AS AssessmentStatus
	,ISNULL(rpt.CreatedDate, '') AS CreatedDate
	,ISNULL(rpt.StartDate, '') AS StartDate
	,ISNULL(rpt.CompletedDate, '') AS CompletedDate
	,ISNULL(rpt.TimeTaken, '') AS TimeTaken
	,ISNULL(rpt.TestTakenCount, '') AS TestTakenCount
	,ISNULL(rpt.NoOfRetest, '') AS NoOfRetest
	,ISNULL(rpt.Rating, '') AS Rating
	,ISNULL(CONVERT(VARCHAR(50), rpt.MgrAtAssessmentUserId), '') AS MgrAtAssessmentUserId
	,ISNULL(mgrAtAssessmet.UserName, '') AS MgrAtAssessment
	,ISNULL(rpt.PrimaryRoleAtAssessment, '') AS PrimaryRoleAtAssessment
	,ISNULL(CONVERT(NVARCHAR(10), rpt.CenterSecondaryRoleIdAtAssessment), '') AS CenterSecondaryRoleIdAtAssessment
	,ISNULL(srm.NAME, '') AS SecondaryRoleAtAssessment
	,ISNULL(CONVERT(NVARCHAR(10), rpt.FacilityIdAtAssessment), '') AS FacilityIdAtAssessment
	,ISNULL(fm.NAME, '') AS FacilityDescAtAssessment
	,ISNULL(rpt.ServiceTowerAtAssessment, '') AS ServiceTowerAtAssessment
	,ISNULL(rpt.RegionAtAssessment, '') AS RegionAtAssessment
	,ISNULL(rpt.CountryAtAssessment, '') AS CountryAtAssessment
FROM CTE rpt
--INNER JOIN @tblSelectedFacilityId tsfi ON tsfi.FacilityId = rpt.FacilityId
--INNER JOIN @tblSelectedCenterSecondaryRoleId tscsri ON tscsri.CenterSecondaryRoleId = rpt.CenterSecondaryRoleId
--INNER JOIN @tblSelectedCenterPguSkillId tscpsi ON tscpsi.CenterPguSkillId = rpt.CenterXPGUXSkillId
INNER JOIN dbo.Center c ON rpt.CenterId = c.CenterId
INNER JOIN dbo.[User] mgru ON rpt.MgrUserId = mgru.UserId
INNER JOIN dbo.[User] engu ON rpt.EngUserId = engu.UserId
INNER JOIN dbo.CenterXPGUXSkill cps ON cps.CenterXPGUXSkillId = rpt.CenterXPGUXSkillId
INNER JOIN dbo.Skill s ON s.SkillId = cps.SkillId
INNER JOIN dbo.CenterXPGU cp ON cp.CenterXPGUId = cps.CenterXPGUId
INNER JOIN dbo.PGU p ON p.PGUId = cp.PGUId
LEFT JOIN dbo.Wave w ON rpt.WaveId = w.Id
LEFT JOIN dbo.AssessmentStatusMaster asm ON rpt.AssessmentStatusId = asm.Id
LEFT JOIN dbo.[User] mgrAtAssessmet ON rpt.MgrAtAssessmentUserId = mgrAtAssessmet.UserId
LEFT JOIN dbo.SecondaryRoleMaster srm ON srm.Id = rpt.CenterSecondaryRoleIdAtAssessment
LEFT JOIN dbo.FacilityMaster fm ON fm.Id = rpt.FacilityIdAtAssessment
ORDER BY c.CenterName
	,w.NAME
	,mgru.UserName
	,engu.UserName
	,p.NAME
	,s.SkillName