--This Example uses TempTables Created by "MAX() vs TOP 1 .. ORDER BY"
--Copy and Paste into SSMS and run against the existing ##tmpNames and ##tmpDetails

;WITH cteYearlyPercentage AS
(
	SELECT
	FullName = N.sFName + ' ' + N.sLName
	,Dept = D.sDept
	,Client = N.sClient
	--,ClientHoursPerYear = SUM(D.iHours) OVER (PARTITION BY N.sClient,YEAR(D.dtDate))
	--,ClientHoursTotal = SUM(D.iHours) OVER (PARTITION BY N.sClient)
	,EmployeeTotalHours = SUM(D.iHours) OVER (PARTITION BY N.sFName + N.sLName)
	,YearlyAsPercentOfTotal = CAST(100 * SUM(D.iHours) OVER (PARTITION BY N.sClient,YEAR(D.dtDate)) / (1. * SUM(D.iHours) OVER (PARTITION BY N.sClient)) AS NUMERIC(10,2))
	,OccuranceDate = CONVERT(CHAR(10),D.dtDate,101)
	FROM
	##tmpNames N
	INNER JOIN
	##tmpDetails D
		ON N.pkAutoNum = D.iFKNames
)
SELECT DISTINCT
FullName
,Client
,PercentageValue = YearlyAsPercentOfTotal
,MonthAndYear = DATENAME(MONTH,OccuranceDate) + ' / ' + CAST(YEAR(OccuranceDate) AS CHAR(4))
FROM
cteYearlyPercentage
WHERE
YearlyAsPercentOfTotal < 30
AND Dept = 'IT'
AND FullName LIKE 'Marge%' 
;