declare @odate datetime set @odate = '2011-04-18'
declare	@m1stdte as datetime
declare	@m2stdte as datetime
declare	@m3stdte as datetime
declare @reportdate as datetime
declare @model as numeric
declare @castdate as datetime
declare @cmstartdate as datetime
declare @cmenddate as datetime

set @castdate = (case	when @odate is not null then @odate
						when datediff(d,DATEADD(mm,DATEDIFF(mm,0,getdate()-1),0),getdate()+1) <= 8 then DATEADD(s,-1,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
						else (SELECT MAX(ReportDate) FROM P.dbo.P WHERE ReportDate <= getdate())
						end)
set	@m1stdte = DATEADD(qq,DATEDIFF(qq,0,@castdate),0)
set	@m2stdte = DATEADD(mm,1,DATEADD(qq,DATEDIFF(qq,0,@castdate),0))
set	@m3stdte = DATEADD(mm,2,DATEADD(qq,DATEDIFF(qq,0,@castdate),0))
set @reportdate = DATEADD(D, 0, DATEDIFF(D, 0, @castdate))
set	@cmstartdate = DATEADD(m, DATEDIFF(m, 0, @reportdate), 0)
set	@cmenddate = DATEADD(s,-1,DATEADD(d, 1, DATEDIFF(d, 0, @reportdate)))
set @model = 29

;WITH emp AS	(
	SELECT DISTINCT	
			EmpID,
			right(ltrim(rtrim(LucID)),7) LucID,
			FName,
			LName,
			Division,
			[Group],
			Team,
			TeamID,
			[Role],
			RoleID
	FROM	P.dbo.PDailyEmp
	WHERE	EmpRoleID = @model AND
			ReportDate = @reportdate
				)

--Name Mask (move the /* */ FROM around the case statements to around the EmpID - [Role] fields to turn masking on)
SELECT	/*(CASE
			WHEN EmpID = @ppnumtest THEN EmpID			
			WHEN TotPctRnk >= .75 THEN EmpID
			ELSE CAST(TotRank as varchar)
			END) as EmpID,
		(CASE
			WHEN EmpID = @ppnumtest THEN o.dbo.ProperCase(FName)			
			WHEN TotPctRnk >= .75 THEN o.dbo.ProperCase(FName)	
			ELSE ''
			END) as FName,
		(CASE
			WHEN EmpID = @ppnumtest THEN o.dbo.ProperCase(LName)
			WHEN TotPctRnk >= .75 THEN o.dbo.ProperCase(LName)
			ELSE ''
			END) as LName,
		(CASE
			WHEN EmpID = @ppnumtest THEN Division		
			WHEN TotPctRnk >= .75 THEN Division
			ELSE ''
			END) as Division,
		(CASE
			WHEN EmpID = @ppnumtest THEN Team			
			WHEN TotPctRnk >= .75 THEN Team
			ELSE ''
			END) as Team,*/
        @reportdate as QTDATE, -- Data Date
		emp.EmpID, -- PPNumber
		o.dbo.ProperCase(FName) as FName, -- First Name
		o.dbo.ProperCase(LName)as LName,  -- Last Name
		Division, -- Division
		Team, -- Team
		TeamID, -- Team ID
		[Role], -- Role
		TotPts, -- MTD Points
		TotRnk, -- MTD Rank
		PctRnk, -- MTD %Rank
		TmPts, -- MTD Team Points
		TmPctRnk, -- MTD Team %Rank
		AHT, -- AHT
		AHTRnk, -- AHT Rank
		AHTPctRnk, -- AHT %Rank
		AHTPts, -- AHT Points
		RetCall,--First Call Resolution (%)
		RCRank,--FCR Rank
		CallTran,--Transfer Rate (%)
		CTRank,--Transfer Rank
		CASE	WHEN @reportdate <= '2010-08-31' THEN CAST(CAST(FCR as decimal(10,2)) as varchar(10)) 
				ELSE CAST(CAST(FCR*100 as decimal(10,2)) as varchar(10)) + '%' 
				END FCR, -- Call Ownership (%)
		FCRRnk, -- CO Rank
		FCRPctRnk, -- CO %Rank (%)
		FCRPts, -- CO Points
		CIQ, -- CIQ
		CIQRnk, -- CIQ Rank
		CIQPctRnk, -- CIQ %Rank
		CIQPts, -- CIQ Points
		Qual, -- Quality Monitoring
		QualRnk, -- Quality Rank
		QualPctRnk, -- Quality %Rank
		QualPts, -- Quality Points
		M1TotPts, -- Month1 Points
		M1PctRnk, -- Month1 %Rank
		M1TmPts, -- Month1 Team Points
		M1TmPctRnk, -- Month1 Team %Rank
		M2TotPts, -- Month2 Points
		M2PctRnk, -- Month2 %Rank
		M2TmPts, -- Month2 Team Points
		M2TmPctRnk, -- Month2 Team %Rank
		M3TotPts, -- Month3 Points
		M3PctRnk, -- Month3 %Rank
		M3TmPts, -- Month3 Team Points
		M3TmPctRnk, -- Month3 Team %Rank
		QAvgPts, -- Quarterly Points
		QPctRnk, -- Quarterly %Rank
		QTmPts, -- Quarterly Team Points
		QTmPctRnk -- Quarterly Team %Rank
		
FROM	emp

	INNER JOIN
		(
	SELECT	EmpID,
			Result TotPts,
			Rank TotRnk,
			PercentRank PctRnk
	FROM	P.dbo.P
	WHERE	ReportDate = @reportdate and
			KPI = 'TotPoints'
		) t
	ON	emp.EmpID = t.EmpID
	
	LEFT OUTER JOIN
		(
	SELECT	EmpID,
			Result TmPts,
			PercentRank TmPctRnk
	FROM	P.dbo.P
	WHERE	ReportDate = @reportdate and
			KPI = 'TeamPoints'
		) m
	ON	emp.EmpID = m.EmpID

	LEFT OUTER JOIN
		(
	SELECT	EmpID,
			Result M1TotPts,
			PercentRank M1PctRnk
	FROM	P.dbo.P
	WHERE	KPI = 'TotPoints' and
			ReportDate =	(
		SELECT	MAX(ReportDate)
		FROM	P.dbo.P
		WHERE	Month = @m1stdte and
				ReportDate <= @reportdate
							)
		)t1
	ON	emp.EmpID = t1.EmpID

	LEFT OUTER JOIN
		(
	SELECT	EmpID,
			Result M2TotPts,
			PercentRank M2PctRnk
	FROM	P.dbo.P
	WHERE	KPI = 'TotPoints' and
			ReportDate =	(
		SELECT	MAX(ReportDate)
		FROM	P.dbo.P
		WHERE	Month = @m2stdte and
				ReportDate <= @reportdate
							)
		)t2
	ON	emp.EmpID = t2.EmpID

	LEFT OUTER JOIN
		(
	SELECT	EmpID,
			Result M3TotPts,
			PercentRank	M3PctRnk
	FROM	P.dbo.P
	WHERE	KPI = 'TotPoints' and
			ReportDate =	(
		SELECT	MAX(ReportDate)
		FROM	P.dbo.P
		WHERE	Month = @m3stdte and
				ReportDate <= @reportdate
							)
		)t3
	ON	emp.EmpID = t3.EmpID

	LEFT OUTER JOIN
		(
	SELECT	EmpID,
			Result QAvgPts,
			PercentRank	QPctRnk
	FROM	P.dbo.P
	WHERE	KPI = 'QTotPoints' and
			ReportDate = @reportdate
		) qa
	ON	emp.EmpID = qa.EmpID

	LEFT OUTER JOIN
		(
	SELECT	EmpID,
			Result M1TmPts,
			PercentRank M1TmPctRnk
	FROM	P.dbo.P
	WHERE	KPI = 'TeamPoints' and
			ReportDate =	(
		SELECT	MAX(ReportDate)
		FROM	P.dbo.P
		WHERE	Month = @m1stdte and
				ReportDate <= @reportdate
							)
		)m1
	ON	emp.EmpID = m1.EmpID

	LEFT OUTER JOIN
		(
	SELECT	EmpID,
			Result M2TmPts,
			PercentRank M2TmPctRnk
	FROM	P.dbo.P
	WHERE	KPI = 'TeamPoints' and
			ReportDate =	(
		SELECT	MAX(ReportDate)
		FROM	P.dbo.P
		WHERE	Month = @m2stdte and
				ReportDate <= @reportdate
							)
		)m2
	ON	emp.EmpID = m2.EmpID

	LEFT OUTER JOIN
		(
	SELECT	EmpID,
			Result M3TmPts,
			PercentRank M3TmPctRnk
	FROM	P.dbo.P
	WHERE	KPI = 'TeamPoints' and
			ReportDate =	(
		SELECT	MAX(ReportDate)
		FROM	P.dbo.P
		WHERE	Month = @m3stdte and
				ReportDate <= @reportdate
							)
		)m3
	ON	emp.EmpID = m3.EmpID

	LEFT OUTER JOIN
		(
	SELECT	EmpID,
			Result QTmPts,
			PercentRank QTmPctRnk
	FROM	P.dbo.P
	WHERE	KPI = 'QTeamPoints' and
			ReportDate = @reportdate
		) qt
	ON	emp.EmpID = qt.EmpID

	LEFT OUTER JOIN
		(
	SELECT	EmpID,
			Result Qual,
			Rank QualRnk,
			PercentRank QualPctRnk,
			Points QualPts
	FROM	P.dbo.P
	WHERE	ReportDate = @reportdate and
			KPI = 'Quality'
		) q
	ON	emp.EmpID = q.EmpID

	LEFT OUTER JOIN
		(
	SELECT	EmpID,
			Result AHT,
			Rank AHTRnk,
			PercentRank AHTPctRnk,
			Points AHTPts
	FROM	P.dbo.P
	WHERE	ReportDate = @reportdate and
			KPI = 'AHT'
		) a
	ON	emp.EmpID = a.EmpID

	LEFT OUTER JOIN
		(
	SELECT	EmpID,
			Result CIQ,
			Rank CIQRnk,
			PercentRank CIQPctRnk,
			Points CIQPts
	FROM	P.dbo.P
	WHERE	ReportDate = @reportdate and
			KPI = 'iCIQ'
		) f
	ON	emp.EmpID = f.EmpID


	LEFT OUTER JOIN
		(
	SELECT	EmpID,
			Result FCR,
			Rank FCRRnk,
			PercentRank FCRPctRnk,
			Points FCRPts
	FROM	P.dbo.P
	WHERE	ReportDate = @reportdate and
			KPI = 'tFCR'
		) c
	ON	emp.EmpID = c.EmpID

	LEFT OUTER JOIN
		(
	SELECT	EmpID,
			RetCall,
			CASE	WHEN RetCall IS NULL THEN COUNT(EmpID) OVER()
					ELSE RANK() OVER(ORDER BY RetCall DESC)
					END RCRank,
			CallTran,
			CASE	WHEN CallTran IS NULL THEN COUNT(EmpID) OVER()
					ELSE RANK() OVER(ORDER BY CallTran ASC)
					END CTRank
	FROM	(
		SELECT	EmpID,
				CAST(Calls - isnull(nullif(FCRCount,0),0) as decimal)/Calls RetCall,
				CAST(isnull(nullif(CTCount,0),0) as decimal)/Calls CallTran
		FROM	emp

			LEFT OUTER JOIN 
				(	
			SELECT	co.LucID
					,Calls
					,isnull(nullif(CTCount,0),0) CTCount
					,isnull(nullif(RCCount,0),0) FCRCount

			FROM				(
					SELECT	LucID
							,SUM(Calls)Calls
					FROM	FirstCallResolution.dbo.COCalls
					WHERE	Date between @cmstartdate and @cmenddate
					GROUP BY	LucID
								) co

				LEFT OUTER JOIN	(
					SELECT	LucID
							,SUM(CTCount) CTCount
					FROM	FirstCallResolution.dbo.CallTransferSum
					WHERE	Date between @cmstartdate and @cmenddate
					GROUP BY	LucID
								) ct
				ON	co.LucID = ct.LucID 

				LEFT OUTER JOIN	(
					SELECT	LucID
							,SUM(RCCount) RCCount
					FROM	FirstCallResolution.dbo.ReturnCallSum
					WHERE	Date between @cmstartdate and @cmenddate
					GROUP BY	LucID
								) rc
				ON	co.LucID = rc.LucID 

				) ca
			ON	emp.LucID = ca.LucID
		WHERE	EmpID in	(
			SELECT	EmpID
			FROM	P.dbo.P
			WHERE	ReportDate = @reportdate and
					KPI = 'TotPoints'
							) and
				Calls > 0 and
				@reportdate >= '2010-09-01'
			) co
		) CO
	ON emp.EmpID = CO.EmpID

ORDER BY	TotPts DESC,
			QAvgPts DESC