USE MSIEnterprise

DECLARE @AvgProfitMargin DECIMAL(5,2)
SET @AvgProfitMargin = (SELECT CAST((SUM(ClientTotal) - SUM(FieldRepTotal))/ SUM(ClientTotal) AS DECIMAL(5,2))
						FROM WorkOrder)

SELECT	f.RepCode																	AS 'Rep Code'

		--Number of orders a rep has worked since Infinity Rollout
		,COUNT(*)																	AS 'Orders'

		--Avg number of orders a rep works per day since hire date or Infinity Rollout
		,CONVERT(DECIMAL(7,2),COUNT(*)) /
		CONVERT(DECIMAL(7,2),
		(GETDATE() - (CASE
		               WHEN f.ActiveDate > '04/01/2010 00:00:000'
		                 THEN f.ActiveDate
		                 ELSE '04/01/2010 00:00:000'
		              END)))														AS 'Orders Per Day'

		/*Calculates a Rep's initial 'open order limit' by taking their existing
		Orders per day and multiplying by 7 days, the idea in the future is to have this
		auto-adjusted based on their performance or 'Rating' */
		,CEILING((CONVERT(DECIMAL(7,2),COUNT(*)) /
		CONVERT(DECIMAL(7,2),
		(GETDATE() - (CASE
						WHEN f.ActiveDate > '04/01/2010 00:00:000'
							THEN f.ActiveDate
							ELSE '04/01/2010 00:00:000'
						END))) * 7))												AS 'Order Limit'



		--Avg amount of time it takes a Rep to return work
		,AVG(CONVERT(DECIMAL(7,2),(w.RepTurnTime)))									AS 'Avg Turn Time'

		--Number of orders a Rep submits which, at any point in the lifecycle, are Rejected
		,SUM(CASE
				WHEN wc.Comment LIKE '%HAS BEEN REJECTED%'
					THEN 1
					ELSE 0
			 END)																	AS '# Rejected'

		--Percent of a Rep's work that gets Rejected at any point in the lifecycle
		,SUM(CASE
				WHEN wc.Comment LIKE '%HAS BEEN REJECTED.%'
					THEN 1
					ELSE 0
					--have to convert COUNT(*) to floating point variable or function will return ZERO
			 END) / CONVERT(DECIMAL(7,2),COUNT(*)) * 100							AS	'% Rejected'

		--MSI profitability on a Rep's body of work (ClientTotal - RepTotal)/(ClientTotal)
		,CAST((SUM(w.ClientTotal) - SUM(w.FieldRepTotal))/
		SUM(w.ClientTotal) AS DECIMAL(5,2))											AS 'Profit Margin'

		--Percent above the company-wide average profit margin
		,CAST((SUM(w.ClientTotal) - SUM(w.FieldRepTotal))/
		SUM(w.ClientTotal) AS DECIMAL(5,2)) - @AvgProfitMargin						AS '% Above Avg Profit Margin'

		--Rep's earned turn time points toward Total Rating
		,(15 - (AVG(CONVERT(DECIMAL(7,2),(w.RepTurnTime))))) * 3.3					AS 'TurnTime Rating(of 50)'

		--Rep's earned Rejected % points toward Total Rating
		,((10-(SUM(CASE
				WHEN wc.Comment LIKE '%HAS BEEN REJECTED.%'
					THEN 1
					ELSE 0
			   END) / CONVERT(DECIMAL(7,2),COUNT(*))* 100)) * 2.5)					AS 'Rejected Rating(of 25)'

		--Bonus/Penalty based on Rep's profit margin
		,((CAST((SUM(w.ClientTotal) - SUM(w.FieldRepTotal)) /                      --NOTE: this weight chosen because a rep will rarely have a rating above 25 (translates to 63%+ profit margin)
			 SUM(w.ClientTotal) AS DECIMAL(5,2))) * 100 * .4)						AS 'Profit Margin Rating (of 40)'

		/*Algorithm with weighted values to calculate a 'Rating'
		SIMPLE ((15 - AVGTURNTIME)*5) + ((10 - (%REJECTED * 100)*2.5)
		WITH PROFIT MARGIN ((15 - AVGTURNTIME)*3.3) + ((10 - (%REJECTED * 100)*2.5) + ((PROFITMARGIN) * 100 * .4) */
		,(15 - (AVG(CONVERT(DECIMAL(7,2),(w.RepTurnTime))))) * 3.3 +
		((10-(SUM(CASE
				WHEN wc.Comment LIKE '%HAS BEEN REJECTED.%'
					THEN 1
					ELSE 0
			  END) / CONVERT(DECIMAL(7,2),COUNT(*))* 100)) * 2.5) +
			 ((CAST((SUM(w.ClientTotal) - SUM(w.FieldRepTotal)) /
			 SUM(w.ClientTotal) AS DECIMAL(7,2)) ) * 100 * .4)						AS 'Total Rating'


FROM WorkOrder w WITH (NOLOCK) INNER JOIN FieldRep f WITH (NOLOCK)
					ON w.idFieldRep = f.idFieldRep
					INNER JOIN WorkOrderComment wc WITH (NOLOCK)
					ON wc.idWorkOrder = w.idWorkOrder

WHERE f.RepCode != 'TX5536'
--and w.LastActiveDate > MONTH(GETDATE()-2)
GROUP BY f.RepCode, f.ActiveDate
HAVING COUNT(*) > 100
ORDER BY 'Total Rating' Desc